How to Retrieve Date from Table Cell Using PHPspreadsheet

How to retrieve date from table cell using PhpSpreadsheet?

The value is amount of days passed since 1900. You can use the PhpSpreadsheet built-in functions to convert it to a unix timestamp:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value);

Or to a PHP DateTime object:

$value = $worksheet->getCell('A1')->getValue();
$date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);

PhpSpreadsheet passing date format to chart

You may need to apply date formatting to the cell, e.g. :

$worksheet->getStyleByColumnAndRow($column, $row)
->getNumberFormat()
->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);

See supported date formats here.

Please note, that MS Excel does not work with unix timestamps like PHP (meaning the number of seconds since 01.01.1970). Instead, Excel counts
the number of days since 01.01.1900 and works with float values when converting to hours/minutes/secs. Therefore you cannot insert into a cell unix timestamp, but convert it into Excel timestamp before. See PHP conversion functions from and into Excel serial date format.

PHPSpreadsheet formula not working between dates

In an Excel document, dates are stored as numbers, not strings. So you need to pass the correct number to setCellValue().

PhpSpreadsheet provides the utility method Date::stringToExcel() to convert strings to Excel dates. You can use it like this:

$date = PhpSpreadsheet\Shared\Date::stringToExcel('2020-06-24 12:30');
$spreadsheet->getActiveSheet()->setCellValue('A1', $date);

Phpspreadsheet - Time cell retrieved as float

Thankfully I have found the answer just now.

$in = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($time_in);
echo gmdate("g:i a", $in);

Hopefully, this could be useful for others.



Related Topics



Leave a reply



Submit