Forum Discussion

chpsen's avatar
chpsen
Copper Contributor
Sep 14, 2023

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 get:

4/25/2019 13:07

in the destination file.  Again, both files are .xlsx.

 

I then ensured the source column is formatted as a Time field with m/d/yy 13:07 so the copy is now 

4/26/23 13:07

and I get this 

4/25/19 13:07

 

What is going on?  Why is excel not copying exact information from one cell to another?  And how do I fix this?  I'm combining multiple spreadsheets with enough differences that I don't want any solution to be complex.  Seems like Excel should just copy and paste the same date from one to the other.

excel

6 Replies

    • chpsen's avatar
      chpsen
      Copper Contributor
      Sep 14, 2023
      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?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 15, 2023

        chpsen 

        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.

Resources