Converting XLSX date-containing field to a real date

March 14th, 2012 by Alex Leave a reply »

Lately, i was importing an XLSX file using a simple xml-based parser and noticed a strange thing.. The date fields were actually containing integers like 40305, which is actually 2010-05-07 (what??). A bit of investigation, and there’s what that means: the date fields have integers that are the amount of days from 1900-00-00. That means that to convert it to a date we should make

$value = date('Y-m-d', mktime(0,0,0,1,$value-1,1900));

The reason why i subtract 1 from the $value because php counts from 1900-01-01, while XLSX has a date from 1900-00-00. So after that conversion we have a valid date.

Comments are closed.