使用PHPExcel从单元格获取错误的日期

Question

I'm using PHPExcel and trying to get the date values from the cells, but for some reason they give me the wrong date.

$dt_column  = $obj_sheet->getCell('A1');
$unix_date  = PHPExcel_Shared_Date::ExcelToPHP($dt_column);
$date       = gmdate('Y-m-d 00:00:00', $unix_date);

The file is in .xlsx format. The value of the cell, in LibreOffice Calc, is displayed as 1/7/2017, (01/07/2017 on the formula input, don't know if there's any practical difference in handling it on PHPExcel dates).

The returned value given to $dt_column is 42917.

The value attributed to $unix_date is -2208988800.

The value attributed to $date is 2036-02-07 00:00:00.

I tried to manually convert the Excel date values to Unix date values using the formula UNIX_DATE = (EXCEL_DATE - 25569) * 86400, as u'd imagine, same result.

PHPExcel_Shared_Date::$_excelBaseDate is set to CALENDAR_WINDOWS_1900.

How can I get the correct date?

Answered

Answer 1

Excel calculates a date as the addition / substraction of x since 1/1/1900. So your date is 42917 days past 1/1/1900. You can best use that to calculate the correct date.

Example:

$d = strtotime("1900-1-1 + ".$dt_column." days");

$d will be the timestamp in UNIX format which can be used with the date() function to format.

Good luck!