Converting PHP cell value to date

Question

PHPExcel is giving me different date value formats on same sheet template

This is the code I use

$value = $this->worksheet->getCellByColumnAndRow(11, 1)  ->getFormattedValue();
$this->_date = DateTime::createFromFormat("j/n/Y",$value);

I've tried the following answers but they didn't solve it

PHPEXCEL get formatted date as is visible in excel file

how to get date from excel using PHPExcel library

This is a problem I encountered and I hope I can help somebody else who also has this problem.

score 1 · Accepted Answer · answered Sep 23 '16 at 11:09

You can convert an Excel serialized timestamp value directly to a PHP DateTime object, without needing to create a formatted string, and then create a new DateTime object from that:

$msExcelSerializedTimestampValue = $this->worksheet
    ->getCellByColumnAndRow(11, 1)
    ->getCalculatedValue();  // getValue() is even easier if the cell doesn't contain a formula
$this->_date = PHPExcel_Shared_Date::ExcelToPHPObject($msExcelSerializedTimestampValue);

score -1 · Answer 2 · answered Sep 23 '16 at 09:36

This solved it for me

$value = $this->worksheet->getCellByColumnAndRow(11, 1)  ->getCalculatedValue();
$timeValue = (string)PHPExcel_Style_NumberFormat::toFormattedString($value,PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
$this->_date = DateTime::createFromFormat("Y-m-d",$timeValue);

Converting PHP cell value to date

2 Answers2