Excel 2008, Copy / Pasting date field, wrong data….

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

Excel Date Problem - Preference Screen

You then need to open up the ‘Calculations’ section of the preferences.

Excel Date Problem - Calculations Screen

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.

17 thoughts on “Excel 2008, Copy / Pasting date field, wrong data….”

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

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

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

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

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

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

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

  8. Is there a way once you have clicked off the “Use the 1904 Date System” to keep that as the default setting?

  9. It doesn’t solve it either! Be careful guys! Some dates may be corrected but take note of the other dates that are already present in the file that you copy data to, they change to 4 years back!

  10. The fix you mention does not work on Excel 2008. The “Use 1904” box has never been checked yet it continues to make the four year date changes when the sheet is copied.

    I have tried checking the box, saving then unchecking the box and saving, even closing Excel and nothing ever changes. I have to send the file to my PC, make the copy page change and then send it back to my Mac.

  11. Many thanks! All of a sudden I found myself wondering how my dataset contained dates from the future. Most useful post I’ve read in a while!

Leave a Reply

Your email address will not be published. Required fields are marked *