reading xls date in php

Question

I'm using php-excel-reader to read an XLS file in my php script, everything is working fine, except for reading a date. It simply return an undefined object.

• The XLS file isn't made on my computer and I don't know with witch version it was created.
• If I open the file on my computer and save it again, everything works fine. (but I would obviously prefer to avoid having to do that)
• After doing some digging in the php-excel-reader script, I managed to get the value it extracts from the XLS. For example, 41397 instead of 03/05/2013 (d/m/y)

A few questions :

• Is this fixable ?
• Is it possible to exploit the 41397 ? Is it a known date format ?
• Is there another xsl to php script where this will work without having to change anything ?

Answer 1

According to excel format 41397 is 2013-05-03

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day:
ddddd.tttttt . This is called a serial date, or serial date-time.

You can use the following code to convert the number to valid date

function excelDateToDate($readDate){
    $phpexcepDate = $readDate-25569; //to offset to Unix epoch
    return strtotime("+$phpexcepDate days", mktime(0,0,0,1,1,1970));
}

php-excel-reader is supposed to do this, but don't know why it is not doing it.

You can get more info about how excel stores date here (not an authentic reference like msdn)

Edit: Checked PHPExcel, looks like the static function PHPExcel_Shared_Date::ExcelToPHP($dateValue = 0, $adjustToTimezone = FALSE, $timezone = NULL) does this.

Answer 2

An easy way...

<?php $date = date_create('30-12-1899'); date_add($date, date_interval_create_from_date_string('41397 days')); echo date_format($date, 'Y-m-d'); ?>