Forum Discussion
Pasted date off by 4 years from source cell
I am copying a set of data from one .xlsx file to another, and the date/time when pasted is exactly 4 years off. When I copy from the source: 4/26/2023 13:07 and paste it into another file I...
Both workbooks use different date systems.
- OK, that sounds like the cause. So it's the numeric date that is off I presume? How do I paste the dates in the correct format? After all, the document you linked to says:
"When you copy dates from a workbook created in an earlier version to a workbook created in Excel 2016 or 2011 for Mac, they will be converted automatically unless the option to "Automatically convert date system" is disabled in Preferences > Edit > Date Options. If this option is disabled, you will receive a message asking whether the dates should be converted when pasted. You have two options. You can convert the dates to use the 1900 date system (recommended). This option makes the dates compatible with other dates in the workbook. Or, you can keep the 1904 date system for the pasted dates only."
My dates were neither automatically corrected nor do I get any message about what I wanted to do, despite what the document says. What next?Enter the number 1462 in an empty cell (this is the number of days between the dates).
Copy this cell.
Select the cells the are 4 years early.
Press Ctrl+Alt+V, or right-click in the selection and select 'Paste Special...' from the context menu, or click the lower half of the Paste button on the Home tab of the ribbon and select 'Paste Special' from the drop-down menu.
Select Add.
Click OK.
You can now clear the cell in which you entered 1462.
- Perfect. Thank you so much! I didn't know about the paste + Add function. it's helpful. In my case, due probably to leap years, I had to add 1461, but it worked. I really appreciate it.
- I have no personal expierence with the 1904 date systen and I will never use it because of the trouble it can cause.
Perhaps someone else could help you further,- Regardless, I really appreciate your pointing me in the right direction. You were right about the problem. Cheers!
