Copying dates between 1900 and 1904 date system workbooks

 

We have already learned about two different date systems used in Excel. Click the following link to learn more about Excel 1900 date system and Excel 1904 date system and how to change Excel date systems.

When copying date values between two Excel workbooks, one is using Excel 1900 date system and other is using Excel 1904 date system, change in date value can happen.

Refer below images. We have two Excel workbooks, one is using Excel 1900 date system and other is using Excel 1904 date system.

To understand about a possible error which can happen while copying date values between Workbooks using Excel 1900 date system and Excel 1904 date system, let us enter a sample date December 5, 2021 of a Cell in a Workbook which is using 1904 date system.

Now, copy and paste the date from Workbook which is using Excel 1904 date system to another Workbook which is using Excel 1900 date system.

Visit the following lessons, if you want to learn more about different date systems in Excel and how to change date systems in Excel.

Once the date value is pasted in the Cell of another Workbook, which is using a different date system (1900 date system, in this case), you can immediately notice that the date value has changed.

We had already learned that there is a difference of 1462 days (four years and one day) between Excel 1900 date system and Excel 1904 date system. Click the following link to learn about difference between Excel date systems.

Note the two points below.

• When a date value is copied from a Workbook using Excel 1904 date system and pasted to a Workbook using Excel 1900 date system, the date is changed to a past date with a difference of 1462 days.


• Similarly, when a date value is copied from a Workbook using Excel 1900 date system and pasted to a Workbook using Excel 1904 date system, the date is changed to a future date with a difference of 1462 days.

This mistake happens because, Excel stores the date value internally as a date serial number and this date serial number is not changed when you copy and paste it between Workbooks using Excel 1900 date system and Excel 1904 date system. But, there is a difference of 1462 days between two Excel date systems.

How to correct date value copy mistake between 1900 and 1904 date systems

Note below points when you correct date value copy mistake between 1900 and 1904 date systems.

• If you want to copy a date value from a Workbook using Excel 1904 date system and paste it into a Workbook using Excel 1900 date system, you need to add 1462 to the date serial number.


• If you want to copy a date value from a Workbook using Excel 1900 date system and paste it into a Workbook using Excel 1904 date system, you need to subtract 1462 to the date serial number.

Follow below steps if you want to correct the date value copy mistake between 1904 and 1900 date systems.

Step 1 - Enter the numeric value 1462 in an empty Cell. Copy the Cell containing the numeric value 1462 and then right-click the Cell where the data value is showing wrongly. Click "Paste Special" from the context menu.

Step 2 - In "Paste Special" dialog box, select "Values" and then "Add". Click "OK" button.

Step 3 - You can see that the mistake is corrected as shown below.