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.