Excel Dates Incompatible from PC to Mac?

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:

before.png

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”:

Workbook Options

Your PC originated spreadsheet and your Mac originated spreadsheet will now match.

after.png

If you care, you can read a few more gory details on this Microsoft article on the date system.

13 thoughts on “Excel Dates Incompatible from PC to Mac?

  1. 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!

  2. Thanks soooooo much!!!!!!! I too was completely frustrated with this bug……until I stumbled on your fix.

    1. @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.

  3. 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.

  4. 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.

  5. I am unable to format the data entry of date in the dd/mm/yyyy format in the Excel for Mac 2008. I am going bananas! Can somebody help please!!!

  6. This is more than an inconvience and I don’t understand why Microsoft has not been forced to fix this or hasn’t been sued by someone who lost data. In our laboratory We exchange Excel files between multiple users and sometimes between users in other countries. A mixture of Macs and PCs are used. Only recently did this issue come to light when we copied a file between the two systems. Although it is fixable, it can be disasterous if someone who does not know what dates are correct opens the file and adds new information with dates. this file will then have two sets of dates, one correct and the other not. On moving back again the issue becomes almost unfixable. The engineers (ha) at Microsoft must live in a secluded world with blinders on where they assume veryone works on their own systems and does not share with others. This is more than ridiculous and I spend a good part of my time ensuring the integrity of our data. Microsoft knows about the issue, they created the program for both platforms, they could easily fix the issue by making one of them compatible with the other. The idiots need to be sued. At the very least, I would suggest Open Office unless you need to use the MS software.

  7. don’t bash MS. for this one. This is Apple chosing to swim upstream. 1900 makes more sense than 1904 for a start date and this format was used FIRST Mac OS went against the grain.

Comments are closed.