Excel date conversion using PHP Excel

Question

i am reading date from excel which is in this format 12/5/2012 day/month/year using this code to read . using PHP EXCEL

   PHPExcel_Style_NumberFormat::toFormattedString($value['A'],'YYYY-MM-DD' );

its working like charm converting the above date '12/5/2012' to '2012-12-05'

now the problem is if the date is lets says 18/5/2012 or you can say if i set day greater than 12 it gives me this date 18/5/2012 in this format 18/5/2012 after formating

i tried this thing as well

      $temp  = strtotime(  PHPExcel_Style_NumberFormat::toFormattedString($value['A'],'YYYY-MM-DD' );
      $actualdate = date('Y-m-d',$temp) ;

THis is also converting the date '12/5/2012' correct but in this case 18/5/2012 it gives output as 1970-01-01

Answer 1

Please use this formula to change from Excel date to Unix date, then you can use "gmdate" to get the real date in PHP:

UNIX_DATE = (EXCEL_DATE - 25569) * 86400

and to convert from Unix date to Excel date, use this formula:

EXCEL_DATE = 25569 + (UNIX_DATE / 86400)

After putting this formula into a variable, you can get the real date in PHP using this example:

$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo gmdate("d-m-Y H:i:s", $UNIX_DATE);