Here’s another great head scratcher in the office debacle – why when you are copying date fields from one sheet to another does Excel feel the need to change the date? It all about the 1904 date format you see. All new sheets created in Excel are automatically set to the 1904 date format whereas all previous versions of Excel were set to the 1900 Date format – it’s an easy fix.
In the sheet that you wish to copy the date to, open up the preferences (Excel -> Preferences).
You then need to open up the ‘Calculations’ section of the preferences.
Then simple select and uncheck the ‘Use 1904 Date System’ – hey presto, if you copy data from your ‘old’ sheet, it will magically appear correctly in the new sheet!
Note: If you are using a Windows computer, you will need to go to Tools -> Options -> Calculation Tab.
I’ve been struggling with this stupid issue for the past few weeks. Now I find out that it’s a “feature” and not a bug? Who gives a shit about macs that are so old, they can’t recognize dates prior to 1904? What percentage of Excel users will ever enter a date prior to 1904? Why are 99% of Excel 2008 users being subjected to this default functionality, when it is a detriment to our work? Leave it to Microsoft to create software that makes the Mac *less* useful.
Thank you – this issue has been causing huge problems in a new template design I was creating. It was a relief to “untick” that 1904 box – instant fix.
Thank you!!!! I have been having this problem for a while now (since I started using Office 2008) and all this time I just manually reinserted the dates (how tedious!). But right now I’m working on a rather large data, so it’s no good if I have to manually change it one by one. So thanks a lot for the info!
Actually, this is Microsoft stupidity carried into both Windows and Mac OSX ports. This is equal opportunity foolishness. This was the default setting on my Windows XP copy as well as my Mac copy.
Why does Microsoft think they have to “help” people”??? The people they’re “helping” are those with limited user skills, and their “help” does nothing but frustrate those of us who don’t need it. This date issue is a perfect example, as are other features that anticipate what they think you want, leading you down a path where you have to constantly undo all of the “help” they provide. If someone has an old system that can’t recognize dates prior to 1904, they’re also dealing with plenty of other functionality issues, so why should the rest of us suffer so that they have one less issue to deal with?
I hate Microsoft.
oh my god, thank you SO much for posting this. I just tore my hair out for an hour.
Yeah, thanks. I was wondering why excel was pasting the wrong dates into my new sheet. This entry seems to be the only coherent fix I could find off of Google.
Thank you so very much! I’ve been trying to combine worksheets from separate workbooks into one workbook, and the date change error was creating a nightmare. The data is too extensive to change one by one, so I am thrilled that you figured it out and published it for the rest of us! Thanks!
Thank you so much for writing this!!! We were going crazy trying to figure out this issue, and it was such a simple fix!!!
Thank you! I would never find that out by myself! Can’t believe that’s a default option!!
Is there a way once you have clicked off the “Use the 1904 Date System” to keep that as the default setting?
Once you close the preference dialog box, it’s saved as far as I know.