Strange Date Issues

Topics: User Forum
Jan 12, 2009 at 11:47 PM
I have a script that extracts the data out of excel files and places it into a database.  Everything was working just fine until I tried to import a particular excel file.  All the dates were way off.  Every other excel file I had tested with this script worked without issue.  To debug I decided to use the HTML writer, I figured it know how to handle the excel dates and maybe I made an error.  To my surprise, all the dates were off by one day in the HTML, 12-31-72 instead of 1-1-1973.  I pulled in a file which worked in my script, and the dates were displayed as an integer.  I can't figure out what has gone wrong, any ideas?
Jan 13, 2009 at 12:00 AM
Edited Jan 13, 2009 at 12:00 AM
Mark has noted similar weirdness here:

Your observation would fit with Mark's if
1. Excel file is of format xlsx
2. Your Excel file was created on Mac (which uses 1904 date mode)
3. You are viewing the Excel file in Excel 2003 together with Office 2007 compatibility pack

Can you confirm this? Also try to open your Excel file in MS Office 2007 if you have the chance and compare date.

This weirdness needs to be clarified.

Jan 13, 2009 at 12:25 PM

As Erik has indicated, I've noticed some oddities (resulting in a single day discrepancy) with writing workbooks when the Mac 1904 calendar is set.

I've not yet done any testing with the reader, mainly because I don't have access to a copy of Excel on a genuine Mac; and while I can create workbooks in Windows Excel with the calendar set to Mac 1904, I'm not sure that I fully trust doing that.

It is an issue that I'm actively investigating at the moment.

Jan 13, 2009 at 3:32 PM
Edited Jan 13, 2009 at 3:34 PM
The file was originally created in excel 2000, on Windows XP.  One of our clients imported some data into it.  I am not sure how he entered the data, because the dates were stored as strings, not integers.  The data was copied to our new import file, the sales person insists on sending the old import file format, but that's a whole different issue.  This new file was created in excel 2007, the data copied, and saved as an .xlsx.  After the data was in the new file, we noticed the dates.  I had to double click on each cell and hit enter to change them to excel dates from the string.  I don't think it was ever touched by a Mac, as the dates are not off by four years only one day and the "use 1904 date system" was never checked.  I do have access to a Mac, at home, with office 2008 on it.  I would be happy to create some excel files for testing.

Here is some more info about the issue, for most of the excel files this code works:
date_format(date_create('@'.PHPExcel_Shared_Date::ExcelToPHP($temp)), 'm/d/Y')

Where $temp is the value of the cell, for dates its an integer.  However with this file, the dates outputted were all wrong, this is actually what lead me to use the HTML writer.  I figured it knew how to handle the date fields better then I did. But it turns out that I could use this code instead and get the correct dates:
date_format(date_create('@'.$temp), 'm/d/Y')

With the first line of code the excel date of 1/1/1973 comes out as 1/24/2026
Where as the other line, the excel date of 1/1/1973 comes out as 1/1/1973

On the exact same file the HTML writer still outputs 12-31-72, I just double checked it.

Jan 13, 2009 at 5:35 PM
Would it be possible to send me a copy of the file, or attach it to the related work item (stripping out any sensitive data)

And any Mac-created Workbooks using date functions that you're happy to donate will be gratefully received
Jan 13, 2009 at 5:42 PM
Edited Jan 13, 2009 at 5:44 PM

Attached is the file, I changed the sensitive data.


Jan 13, 2009 at 5:54 PM
I don't know if the attachment survived or not, if not where should I send it?

As for the Mac Excel dates, if you had a list, or a PC created excel with what you want, I can build it.  I don't do much with excel, so I don't really know what it can do with dates, other then give me problems. If all you need are a few dates typed in, I can do that too.