I discovered an amazing “bug” in excel tonight. I needed to create a copy of part of a spreadsheet into a new sheet. See if you can spot the difference between the original on the left and the new spreadsheet on the right:
You’ll notice that the dates are off by exactly 4 years and 1 day after a simple cut-and-paste into a fresh spreadsheet. It took a little while to figure out why this was happening.
First, a little background. When you write a date in excel, it stores the date as the number of days that have passed since either Jan 1, 1900 (on the PC) or since Jan 1, 1904 (on the Mac). Not surprisingly, this can cause problems between the two systems.
So, if you open a PC originated spreadsheet on a mac, it automatically uses the PC starting date (1900). If, however, you happen to make a new spreadsheet on that mac and cut-and-paste the dates to that new sheet, you’ll notice that the dates are off by 4 years and 1 day. Why the one day? Apparently that came down to a leap-year related difference between the two systems.
Fortunately, it’s easy to fix. If you’d like to conform your Mac spreadsheet to the PC system, simply open up Preferences->Calculation->Workbook Options an uncheck “1904 Date System”:
Your PC originated spreadsheet and your Mac originated spreadsheet will now match.
If you care, you can read a few more gory details on this Microsoft article on the date system.
A little odd but its a proven fact Microsoft enjoys complication.
Oh my God — Thank you! This has driven me nuts. I’m on a Mac exchanging Excel income and expense statements with our Financial Manager who’s on a PC, and this happens every time. I keep changing the dates, but now the problem is cured!
Thanks soooooo much!!!!!!! I too was completely frustrated with this bug……until I stumbled on your fix.
your brilliant
thank you so much!
Is there a good source to find other mac/pc differences?
@vicky: Other than the differences in scripting the two, I think they are mostly the same. I haven’t found a great site for seeing all the differences side-by-side.
I was also surprised by these complicated engineering choices from Microsoft. This is not a feature it is a BUG, since interoperability and consistency across platforms should be a priority for MS Office!
The workaround mentioned here is only palliative since the actual problem appears any time we open excel files created on other platforms.
PS: By the way, OpenOffice.org works quite fine on the Mac OS X. Despite certain limits on the set of features I experience that OpenOffice delivers a safer, more stable, more flexible, and cheaper office solution, specially when used on Macs. There are a few weak points though, but very few things in this life are completely perfect.
(Sorry by those typos and grammar errors on the previous posting!)
I have discovered that the preference is not “sticky” — I have to do it with every spreadsheet. So I have the 1904 box unchecked, but when I copy the sheet into a new workbook the dates revert and I have to uncheck the box again. Seems there is no permanent fix.