Date values are off by one.

May 11, 2009 at 4:21 PM

I am having a problem with getting the correct dates from the cell using the 1.6.6 .  I have two cells in the worksheet.  One is 06/07/2009 and the other is a function with =Today().

The first problem is determining when to use PHPExcel_Shared_Date::ExcelToPHP and when not to.  For instance when =Today you don;t; but for 06/07/2009 you do??

cell1 = =Today()

cell2 = 06/07/2009

Expected Results: Today = 2009-05-11

 print "date1: " . date ( 'Y-m-d', $cell1->getCalculatedValue());  shows 2009-05-10

 print "date2:" . date ( 'Y-m-d', PHPExcel_Shared_Date::ExcelToPHP( $cell2->getCalculatedValue()));

The results are that both dates are off by one.

date1: 2009-05-10

date2: 2009-06-06

 

Any ideas on what I am doing wrong? 

What is the best logic to determine if need to use PHPExcel_Shared_Date::ExcelToPHP to calculate the date.

 

Thanks

May 11, 2009 at 7:26 PM

I am able to get the correct dates if I modify PHPExcel/Shared/Date.php 

function ExcelToPHP

and change

            //$myExcelBaseDate = 25569;


to            $myExcelBaseDate = 25568;

 

Developer
May 12, 2009 at 2:43 AM
The first problem is determining when to use PHPExcel_Shared_Date::ExcelToPHP and when not to.  For instance when =Today you don;t; but for 06/07/2009 you do??

Yes, there is ambiguity, and it should probably be removed. Work item created for this:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=9960

Your solution for now is to put this at the beginning of your script:

PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);

The results are that both dates are off by one.

The problem is that the date() function in PHP uses your system time zone setting.
http://php.net/manual/en/function.date.php

PHPExcel_Shared_Date::ExcelToPHP() assumes that the date supplied is GMT and returns the Unix time accordingly.

As a consequence, you should use gmdate() instead of date(), otherwise you may get a 1 day offset.
http://php.net/manual/en/function.gmdate.php

print "date1: " . gmdate ( 'Y-m-d', $cell1->getCalculatedValue());
print "date2:" . gmdate ( 'Y-m-d', PHPExcel_Shared_Date::ExcelToPHP( $cell2->getCalculatedValue()));

I am able to get the correct dates if I modify PHPExcel/Shared/Date.php

Ugg... Not recommended.