checking USE 1904 DATE SYSTEM changes the rest of my data

I have 2 files and im copying date from File1 to File2. Pasting the date to FILE2 adds 4 years. went to Preference checked the USE 1904 DATE SYSTEM and it copy the date perfectly but all my dates in ...

excel

Haytham Amairah

Silver Contributor

Jan 19, 2018

Katrina,

You have two workbooks with two different date systems.

File1 with (1900 date system)

File2 with (1904 date system)

What you did is:
You unchecked the (Use 1904 date system) in file2, to be able to copy a date from File1 to File2 without any changing it, but this option affects other dates in File2 so the dates are shifted by four years and one day (1462 days).

Please be aware that this change happened only once!

For the workaround

You have to add (1462 days) to each date from the date that changed as below steps.

Select any empty cell, type 1462, and then copy this cell.
Select the dates that changed, and then press Ctrl+Alt+V to open the Paste Special dialog box.
Choose Values and Add, and then hit OK.

I advise you to stick with the 1900 date system if you're using Excel for Windows because it's the primary date system in Excel for Windows, and it's compulsory default date system, so any new workbook will be in this date system.

While the 1904 date system is for Excel on Mac, and the option to change to this date system in Windows version of Excel is just compatibility option.