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
Laravel: How to Use Multiple Pivot Table Relationships
How to Use PHPize After Update to MACos Mojave
Order Properties Should Not Be Accessed Directly - Woocommerce 3.0
Str_Replace() for Multiple Value Replacement
How to Add/Set Images on PHPoffice/Phpword Template
Howto Generate JSON with Smarty
Maximum Function Nesting Level of '100' Reached, Aborting After Upgrading to Laravel 5.1
PHP Looping Through Multiple Arrays
PHP - Display a 404 Error Without Redirecting to Another Page
File_Get_Contents(): Stream Does Not Support Seeking/When Was PHP Behavior About This Changed
Shortcodes Inside a Shortcode - Wordpress
Facebook Like Custom Profile Url PHP
Usort Issue with Decimal Numbers
Creating and Invoking an Anonymous Function in a Single Statement
Unchecked Checkbox Returning Null Value
PHP Datetime::Createfromformat Doesn't Parse Iso 8601 Date Time