1904 Date System
Excel actually supports two dates systems: A 1900 date system and a 1904 date system.
The 1900 date system has always been the default if you are using Excel for Windows.
The 1900 date system is also the default if you are using Excel 2011 for Mac or a later Office for Mac version.
The Excel 2008 for Mac and earlier versions used a 1904 date system.
Whats the Difference
The difference between the two date systems is the starting date for the serial numbers.
The 1900 date system uses 1st January 1900 as serial number "1".
The 1904 date system uses 2nd January 1904 as serial number "1".
You can change which date system you want to use in a workbook from your (Excel Options).
It is possible to switch to the 1904 date system to provide compatibility with earlier versions of Excel for Mac files.
link - learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system
link - support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487
Check your Options
You can switch to the 1904 date system by selecting (Excel Options)(Advanced tab, "Use 1904 date system").
You should be careful if you are using different date systems in workbooks that are linked together.
The workbooks will contain the same serial numbers but they will represent different dates.
When you change the date system the date serial number in your workbook will not change but the date displayed will change.
Using 1904 Dates
If the year is between 4 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year.
For example, DATE(100,1,2) returns January 2, 2000 (1900+100).
If the year is between 1904 and 9999 (inclusive), Excel uses that value as the year.
For example, DATE(2000,1,2) returns January 2, 2000.
If the year is less than 4, then #NUM! is returned.
If the year is greater than or equal to 10,000, then #NUM! is returned.
If the year is between 1900 and 1903 (inclusive), then #NUM! is returned.
One advantage of the 1904 date system is that it allows you display negative times.
For example, (8:00 PM - 9:00 PM) = -1:00.
If you have entered dates into a workbook using the 1900 date system and then you switch to the 1904 date system then all your dates will be out by 4 years.
You should be very careful if you are using different date systems in two linked workbooks as the same serial numbers will be interpreted as different dates.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext