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 PhpOfficePhpSpreadsheetSpreadsheet; use PhpOfficePhpSpreadsheetWriterXlsx; $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 = PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_DATE_TIME1; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); foreach ($times as $i => $time) { $sheet->setCellValue('A' . ($i+1), PhpOfficePhpSpreadsheetSharedDate::PHPToExcel($time)); $sheet->getStyle('A' . ($i+1))->getNumberFormat()->setFormatCode($format); } $writer = new Xlsx($spreadsheet); $writer->save('test.xlsx');
From PHPOfficePhpSpreadsheetStyleNumberFormat::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:
But if we type in “5:00 AM” into a cell, the formula bar does not include the date:
Here is the screen that pops up from Right Click > “Format Cell”:
Can someone please tell me if I’m doing something wrong, thankyou.
Advertisement
Answer
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 = PhpOfficePhpSpreadsheetSharedDate::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.