Forum Discussion
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 FILE2 changes too.. how can i stop it from automatically changing the existing dates on FILE2?
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.
Hi Sir,
It is very clear message and easy to understand it.
1. is there any chance to plug this error not to occur in future.
2. is there any alternative way to find out the SHORTFALL TIME DIFFERENCE
(not using of TEXT(ABS(A1-B1),"-HH:MM". because the result is giving the Text value
but not the INT value.
Thank you Sir. (and this is very long gap reply... I agree on it)
Haytham Amairah I've been using Excel on Mac systems since it came out on the original Mac. It was only today that I realized that Windows and Mac use different date systems. Surely MS could write code so that their file formats really are compatible across operating systems. Unfortunately the current implementation is a real kludge.
I'm running Excel for Mac Version 16.52 using a MS 365 subscription. I'm running on MacOS 11.5.2 (Big Sur).
It seems that Excel now defaults to the 1900 date system rather than defaulting to the 1904 system as in the past and the setting for the date system in Preferences doesn't seem to persist. I'm using some old (many years) Excel worksheets with date functions such as Today() and noticed the results were off by the 4 years. I'm using the Today() function with a VLOOKUP to a table in an external worksheet.
Those exact same worksheets have worked correctly for many years. The problem seems to be that the current version of Excel defaults to the Windows 1900 date system rather than the MacOS date system. There's a check box in the Mac version under Preferences>Computation that when checked. uses the 1904 system. Unfortunately it's defaulting to "not checked" every time I open the spreadsheet.
Is this the proper behavior? Or is it a bug that's crept into the recent release?
I suspect it's a bug because the problem's not cropped up before and I use these sheets multiple times each week. So now each time I use them I have to check that preference. The workaround you suggest is to remember which system is in which sheet and adjust by 1462 when necessary. But that won't work if the date system used in a particular sheet is no longer persistent.
If this is a bug, what's the Microsoft way of elevating it for repair?
