Skip to content
Advertisement

PHPSpreadsheet: Formatting a cell for time also includes the date

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:

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.

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement