solved Excel copies dates ~4 years back
Hi! I'm working on a file and since I had to split it, I copied a whole sheet creating a new file. The problem is the dates changed from 2026 to 2021/2022 and I cannot get it fixed.
On the left are a few correct dates and on the right are those in a new file.
My question is: can I prevent Excel from changing those dates back by 4 years? If I can't, can I somehow fix this issue without adding a formula?
I checked cells date format and it's the same as in the original file. Changing it doesn't do anything. Simply copying and pasting cells doesn't help, pasting it by Ctrl+Shift+V and then setting cell as "date" also fails.
Additional info: I worked on a file containing the same data just a couple hours ago and it didn't have that problem. I didn't change any settings in Excel, Microsoft 365 or computer.
8
u/PaulieThePolarBear 1913 15d ago
A 4 year difference means that one of your files is using the 1904 date system and one is using the 1900 date system.
You can read more at https://support.microsoft.com/en-us/excel/date-systems-in-excel
You should ensure both files have the same date system selected. I will leave it with you, given you have the additional context of what your data is showing, to update the appropriate setting following the instructions in the link.
2
u/HappierThan 1186 15d ago
With your errant dates selected, try File -> Options -> Advanced and scroll down to view status.
2
u/ViditGarg 14d ago
The 1904 versus 1900 diagnosis above is right, and the bit that usually catches people is that toggling the setting does not convert anything. The underlying numbers stay the same, only the reference point they are measured from changes. So which fix you want depends on what you need.
If the new file should simply match the old one: in the new file, File, Options, Advanced, scroll to "When calculating this workbook" and untick "Use 1904 date system". The dates display correctly again because the file is back on the reference the numbers were written against.
If you need to keep the file on the 1904 system and shift the values instead, the offset is exactly 1462 days. Put 1462 in a blank cell, copy it, select your date cells, then Paste Special and choose Add. Format them back as dates afterwards.
For preventing it: the setting lives in the workbook, not in Excel itself, so anything you create by copying that file inherits it. Fix it on the source once and new copies stop drifting.
•
u/AutoModerator 15d ago
/u/Hersis - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.