Reading Date Value

[GoogleCodeExporter]

I have a spreadsheet with date values in a column. However phpexcel is 
returning a string value instead.

For example, 24/08/2009 returns 40049. Even typing the date value in other 
format like 08/04/2009 returns 40049.

Original issue reported on code.google.com by mrp...@gmail.com on 28 Aug 2009 at 3:20

[GoogleCodeExporter]

Excel dates are stored as an integer (I think it's the number of days since 
Jan. 1,
1970 or something).  This little function used to be on the PHP site which will
convert the Excel date into a PHP timestamp.
function xl2timestamp($xl_date)
{
$timestamp = ($xl - 25569) * 86400;
return $timestamp;
}

Original comment by patha...@twc.com on 30 Aug 2009 at 5:46

[GoogleCodeExporter]

The line
   $timestamp = ($xl - 25569) * 86400; 
must be
  $timestamp = ($xl_date - 25569) * 86400;

Original comment by YamilBra...@gmail.com on 2 Dec 2010 at 4:25

[GoogleCodeExporter]

it's the number of days since dec 30, 1899 (they missed a couple leap years or 
something? or i used a bad date calculator tool).  the 1970 one is the unix 
epoch, and it's in seconds.

Original comment by t045tygh...@gmail.com on 16 Aug 2011 at 4:13

[GoogleCodeExporter]

after looking into this further, i found out that lotus 123, excel's major 
competitor at its introduction, neglected to take into account that 1900 was 
not a leap year.  microsoft made the same mistake on purpose to maximize their 
compatibility with the market leader, and still hasn't fixed it.

Original comment by t045tygh...@gmail.com on 16 Aug 2011 at 7:42

[GoogleCodeExporter]

I encountered the issue too (on imac) and came up with the following solution:
    function get_date_from_excel( $poop ){
        return mktime( 0 , 0 , 0 , 1 , (int) $poop + 1, 1904 );
    }

Note: On iMac the the base year is 1904 (reference 
http://support.microsoft.com/kb/180162/en)

Original comment by yann.le.scouarnec on 5 Nov 2012 at 3:04

[GoogleCodeExporter]

simply use 

function excel_date($xl_date)
{
return $displayDate = PHPExcel_Style_NumberFormat::toFormattedString($xl_date, 
'YYYY-MM-DD hh:mm:ss');
}

Original comment by ma...@rudrainnovatives.com on 26 Sep 2014 at 7:33