PHPExcel Reader : getCell with Date format

Topics: Developer Forum
Jul 13, 2010 at 1:59 PM
Hello, I have looked for this on the internet for the whole day, but I just can't find anything useful. I am trying to parse an Excel 2003 file, and I am having problems to retrieve cells with "Date" format. Instead of returning a date like "26/04/2006", it is returning "38833". How can I do to make PHPExcel return me the date in a correct way ? Thanks !
Coordinator
Jul 13, 2010 at 2:13 PM
Edited Jul 13, 2010 at 2:14 PM

Excel holds dates as a positive floating point numeric value, the number of days since 1st January 1900, with the fractional part representing the time. PHP holds dates as a signed integer value, the number of seconds since/before January 1 1970 00:00:00 GMT, or as a SateTime object. When storing a value to be used as a date using PHPExcel, you need to store the numeric value that Excel will recognise: when reading a date from Excel, you need to convert it to a PHP date/timestamp or DateTime object. Functions to perform these conversions are built into the PHPExcel_Shared_Date class.

$PHPDate = PHPExcel_Shared_Date::ExcelToPHP($excelDate);

$PHPDateObject = PHPExcel_Shared_Date::ExcelToPHPObject($excelDate);

$excelDate = PHPExcel_Shared_Date::PHPToExcel($PHPDate);

$excelDate = PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0);

Once you've got your $PHPDate, you can format it using the standard date() function

 

Alternatively, you can use

$displayDate = PHPExcel_Style_NumberFormat::toFormattedString($excelDate, 'YYYY-MM-DD');

 

using Excel format masks 

Jul 13, 2010 at 2:21 PM
Thank you very much Mark !! It worked like a charm with the examples you provided ! Thank you for this extremely quick answer, you saved my day :D