PHPSpreadsheet: Formatting a cell for time also includes the date

user_1427345 0
Viewed 4735

I am trying to format a time cell using PHPSpreadsheet but it seems to be including the date as well when looking at the formula bar. There also seems to be some inconsistency when converting from a string, datetime object or unix timestamp.

<?php

include '../vendor/autoload.php';

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$times = [
    '16:00:00',
    new \DateTime('16:00:00'),
    \strtotime('16:00:00'),
    '2020-04-04 16:00:00',
    new \DateTime('2020-02-04 16:00:00'),
    \strtotime('2020-02-04 16:00:00'),  
];

$format = \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_TIME1;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

foreach ($times as $i => $time) {
    $sheet->setCellValue('A' . ($i+1), \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($time));
    $sheet->getStyle('A' . ($i+1))->getNumberFormat()->setFormatCode($format);  
}

$writer = new Xlsx($spreadsheet);

$writer->save('test.xlsx');

From \PHPOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_TIME1: const FORMAT_DATE_TIME1 = 'h:mm AM/PM';

Is this a bug or intended functionality? Considering as const FORMAT_DATE_DATETIME = 'd/m/yy h:mm'; does include some date parameters I think there's something wrong happening.

Here's some screenshots of what happens:

Formula bar showing included date

But if we type in "5:00 AM" into a cell, the formula bar does not include the date:

Manually entered value does not include the date

Here is the screen that pops up from Right Click > "Format Cell":

Format Cell screen

Can someone please tell me if I'm doing something wrong, thankyou.

phpspreadsheet excel php
2 Answers

I figured out how to fix this problem: You need to calculate the Excel representation of the timestamp and then get only the numbers after the decimal place.

<?php

$timestamp = new \DateTime('16:00:00');
$excelTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($timestamp);
$excelDate = floor($excelTimestamp);
$time = $excelTimestamp - $excelDate;

From the documentation:

In Excel, dates and Times are stored as numeric values counting the number of days elapsed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can verify this in Microsoft Office Excel by entering that date in a cell and afterwards changing the number format to 'General' so the true numeric value is revealed. Likewise, '3:15 AM' is represented as 0.135417.

I hope this helps anybody else stuck with this problem.

user_14273450

You must set the right number format see for more information the official sample documentation.

The number format that you set is 'h:mm AM/PM' means that there is no date included only the time. When you change to 5 PM you overwrite the content and the number format will used. If you will have a date you should try to set 'd/m/yy h:mm AM/PM'.

user_18086310
Related