Excel Date Weirdness
A strange Microsoft Excel problem got passed up the chain to me today. We had to re-create a workbook that had lots of links to data from other workbooks. Yea, one of those Excel is a database nightmares that I keep wanting to replace, but we never have time or money to do it.
Anyway, the new sheet showed dates that were four years and 1 day older than what they ought to be. So a project with a billing date that should have been January 20, 2006 was showing up as billing on January 19, 2002. Nobody could figure it out, and because it is something pretty critical to our business (billing is about as business critical as it gets) it made it’s way up to me right away.
The dates were formated in different ways in the original spreadsheets, but if you turned them back into plain numbers you could see that they were both coming up as 37275. Anyway to make a long story short, it turns out that Excel has a feature labeled “1904 dates system” which is available under the options menu.
This means that under the covers Excel stores dates as the number of days since January 1 1900. Unless of course you have “1904 date system” turned on, in which case it counts days since January 2nd 1904.
As features go, this one seemed pretty absurd to me, but it turns out that this has something to do with Office on the Mac and some 10 year (at least) bug in Mac OS.
It seems like a little bit of foresight 15 years ago would have prevented Microsoft from having to maintain this feature for I don’t know how many versions of office. But, even though the bug this was originally designed to work around has long been fixed, the workaround is still generating work at Microsoft, and for helpdesk people around the world!
Oh well, at least this kind of thing keeps our lives interesting.
Well, not actually a bug but an odd design decision in MacOS, actually over 20 years ago: The time functions in MacOS returned time as seconds since January 1, 1904, and Excel (originally created on the Mac) used this system initially.
Why 1904? Because 1900, although divisibly by 4, wasn’t a leap year. By using 1904 as a base, they saved a few bytes code, and every byte counted when they tried to squeeze the entire MacOS into 64KiB of ROM…
For the PC version of Excel, Microsoft decided that it would be better to use a more “conventional” base, i.e. 1900. But they didn’t handle this leap year issue correctly, hence it is off by one day.
Even later, they added this option to enable compatibility between Excel sheets between created on PC and Mac…
Thanks for the background info on the design decision by Apple to start counting from 1904. I can understand Apple’s reasoning here, even though it turns out to have been less than optimal for compatibility with the future. But the Microsoft’s design decision that it would be worth their time to maintain two different date systems in excel — for 15+ years! and 10+ product releases — seems a bit absurd.
HI,
So how does one fix the date problem presentation in Excel?
The formula window displays the date format selected, but the cell shows the gibberish numeric sequence!
MS wants to charge $35 just to report the problem to them.
Doesn’t MS ever take responsibility to fix problems?
Thanks, Paula
Paula,
The problem you describe seems quite different than the one above, and it is actually something that you can fix yourself pretty easily.
1) Highlight the cell that should have a date,
2) go to the format menu,
3) click cells,
4) chang the format to Date.
Mac Fix from the Help menu:
Change the date system between 1900 and 1904
1. On the Excel menu, click Preferences, and then click Calculation.
2. Do one of the following in the 1904 date system check box:
• To use the Macintosh standard system (1904), select the check box.
• To use the Windows standard system (1900), clear the check box
to fix the date when importing into Excel from Mac, in Excel just go to tools/options/calculation and click 1904 date system OK
I encountered this bug too, but in automated web submissions. So my priority is simply to identify the spreadsheets that have the date set to 1904 and handle accordingly, either to reject and request the user change their Excel date setting or process knowing how to adjust the dates by code offsets. I haven’t yet found a site that describes where in the Excel file that flag is saved, but for Excel 2003 it appears to be in byte 765. Whether that applies to spreadsheets in other Excel versions, I could only guess.
To fix this in Excel ‘07: office button, excel options, advanced tab, scroll down to “when calculating this workbook”. This has the 1904 / 1900 checkbox.