duongthanhtam2 / php-excel-reader

Automatically exported from code.google.com/p/php-excel-reader
0 stars 0 forks source link

Reading Date Value #42

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
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 commented 9 years ago
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 commented 9 years ago
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 commented 9 years ago
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 commented 9 years ago
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 commented 9 years ago
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 commented 9 years ago
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

GoogleCodeExporter commented 9 years ago
Thank you patha...@twc.com, I solved my issue using the solution posted by you.

Original comment by ylli...@gmail.com on 6 Aug 2015 at 8:43