TODAY() function

Topics: User Forum
Jan 13, 2009 at 1:28 PM
I have a spreadsheet with a cell with the contents '=TODAY()' that works fine in OpenOffice.  (And I assume MS Office, as that's what the client uses.)  When I load the spreadsheet into PHPExcel and ask for the value of that cell, I get the date the spreadsheet was saved, instead of today's date.  (OpenOffice shows today's date.)  This happens both with local documents (loading right from hard drive) and remote scripts (upload via PHP, then load document).  In both cases, I get the date the spreadsheet was saved on my local machine.

I get the same result from GetValue and GetCalculatedValue.

Is this a bug?  Is there a workaround?  Is there a way to refresh the cells of the database?

From what I can tell, the TODAY function just calls the DATENOW function's code, but I couldn't find anything on that either.

If I set the cell to '=DATENOW()' and set the active worksheet again, that cell gets recalculated, but all the others based on it don't.  If I don't set the worksheet, nothing happens.  If I set it to '=TODAY()', I end up with something that is not a date.

I've searched around and didn't find my issue in the forums, issue tracker, or Google.
Coordinator
Jan 13, 2009 at 1:42 PM
getValue() and getCalculatedValue() should not return the same value if the cell contains a formula: getValue() should return the actual formula ("=TODAY()") in this case. Can you get access to MS Excel and confirm that the cell does indeed contain a formula rather than a value.
Jan 13, 2009 at 2:26 PM
When I click on the cell in OpenOffice, it says the contents are '=TODAY()' and it always has the current date when I open it.  I could probably find a computer in the office with the real Excel if it's really necessary to check it, too.  The related cells are like '=B20+1' (to get the next day).
Jan 13, 2009 at 2:44 PM
I just confirmed with a copy of Excel 2007 on Windows that it's the same there.
Coordinator
Jan 13, 2009 at 6:32 PM

The same there meaning that MS Excel works correctly? or that it too has the save date of the document?

If the former, would it be possible to send me a copy of the spreadsheet so that I can run some tests of my own?

Jan 13, 2009 at 9:51 PM
Meaning that MS Excel and OpenOffice behave identically, but PHPExcel does not.

I can't give you that exact spreadsheet, but I will see about making one that I can send, along with some code that shows the issue.
Jan 15, 2009 at 12:56 PM
Edited Jan 15, 2009 at 1:03 PM
Okay, I've created a spreadsheet and code that display the problem that I can share.

The code is below.  The spreadsheet is at:  http://www.mediafire.com/file/1umnuht2nmz/datetest.xls

For some reason, even yesterday, the date was a day off.  Today, it's 2 days off.

The 'formatted/unformatted' stuff was attempting to show another issue I was having, but I didn't manage to reproduce it yet.  It's new since I updated to the newest version of PHPExcel and I haven't tracked it down yet.  (Basically, sometimes I get back a PHP date, and other times an Excel date...  So it's hard to get dates accurately.)

<?php
    error_reporting(E_ALL);
    require_once("PHPExcel.php");
    require_once("PHPExcel/Reader/Excel5.php");
    require_once("PHPExcel/Shared/Date.php");
    $excelReader = new PHPExcel_Reader_Excel5();
    $excelReader->setReadDataOnly(true);
    $excelDoc = $excelReader->load('datetest.xls');
    unset($excelReader);
    $activeWorksheet = $excelDoc->getActiveSheet();
    echo "Unformatted\n";
    $value = $activeWorksheet->getCellByColumnAndRow(0,1)->getValue();
    var_dump($value);
    echo 'Expected: '.date('Y-m-d')."\n";
    echo 'Got: '.date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($value))."\n";
    $value = $activeWorksheet->getCellByColumnAndRow(0,2)->getValue();
    var_dump($value);
    echo 'Expected: '.date('Y-m-d', strtotime('+1 days'))."\n";
    echo 'Got: '.date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($value))."\n";
    $value = $activeWorksheet->getCellByColumnAndRow(1,1)->getValue();
    echo "Formatted\n";
    var_dump($value);
    echo 'Expected: '.date('Y-m-d')."\n";
    echo 'Got: '.date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($value))."\n";
    $value = $activeWorksheet->getCellByColumnAndRow(1,2)->getValue();
    var_dump($value);
    echo 'Expected: '.date('Y-m-d', strtotime('+1 days'))."\n";
    echo 'Got: '.date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($value))."\n";
?>

Edit:  Also, changing it to getCalculatedValue() doesn't change anything.
Developer
Jan 15, 2009 at 10:19 PM
Ok, I notice the problem. As Mark says, getValue() should return the formula when the cell holds a formula.

But in PHPExcel_Reader_Excel5, there is only true formula support for BIFF8 xls files (Excel 97/2000/XP), not for BIFF5 xls files (Excel 5.0 - 95). For the latter ones just the cached (calculated) value is read and not the formula. That explains your results because your datetest.xls if BIFF5.

If you are not sure whether your xls file is of type BIFF5 or BIFF8, you can determine it as follows:

1. Open the xls file in OpenOffice.org 3
2. Choose File > Save As
3. In the "Save As" dialog, do nothing, but just observe what it says in the field "Save as type:"
4. If it says "Microsoft Excel 5.0 (.xls)" then you have a BIFF5 file, if it says "Microsoft Excel 97/2000/XP (.xls)" then you have a BIFF8 file

Screen shot of "Save As" dialog in OpenOffice.org 3
http://img104.imageshack.us/img104/6130/saveaswi2.png

If you save your file as "Microsoft Excel 97/2000/XP (.xls)" then it should work as expected.

The question is now whether we need to add true formula support for BIFF5 ?
Jan 16, 2009 at 12:05 AM
Ahh, I see.  I don't actually have control of the original file...  I have to accept whatever they give me.  I've already contacted the client about not using TODAY() and just typing in a date and I'm waiting for a response. 

Thanks for looking into this, both of you!
Developer
Jan 16, 2009 at 1:13 AM
I have added a work item for this.
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=8932

Please not that it may take time to get this solved. It is not all that simple.

Coordinator
Jan 16, 2009 at 8:45 AM
Edited Jan 16, 2009 at 8:46 AM
>> Basically, sometimes I get back a PHP date, and other times an Excel date...
>>  So it's hard to get dates accurately
>>
This is something that we're current considering how to handle. We ought to be consistent, but this isn't always the case.
As a rule of thumb, if the PHP data type is a float/double, then it's an Excel datestamp; if it's an integer, then it's a PHP datestamp. This behaviour can be controlled for calculated dates by setting the ReturnDateType flag using PHPExcel_Calculation_Functions::setReturnDateType() with allowed values of RETURNDATE_PHP_NUMERIC, RETURNDATE_PHP_OBJECT or RETURNDATE_EXCEL.
With effect from last night's CVS source, dates that had been entered directly into a worksheet using MS Excel, and read by PHPExcel will be correctly retrieved as float, identifying them as Excel timestamps... previously they were being read as integer, leading to some discrepancies because they would be assumed to be PHP timestamps when used in calculations.