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.
Comments
13 responses to “Excel Dates Incompatible from PC to Mac?”
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.
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!!!
Oh, I discovered this problem some time ago and had a lot of troubles. You are the real savior for me!)
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.
Rob, you are a genius. Thanks so much, Roger
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.