1900 and 1904 date systems in Excel
There are two date systems used in Excel. The two date systems in Excel are the 1900 date system and the 1904 date system. When you enter a date value in an Excel Cell, that date value is converted into a number, called as date serial number, that represents the elapsed days starting from January 1, 1900 in 1900 date system and January 1, 1904 in 1904 date system.
Two different date systems were created to make Excel compatible with other spreadsheet programs and operating systems during the early development period of Excel.
Excel Date System | Start Date | Start Serial Number |
---|---|---|
1900 date system | January 1, 1900 | 1 |
1904 date system | January 1, 1904 | 0 |
1900 date system in Excel
Date values in Excel are stored internally as numeric values. The first day of 1900 date system is considered as January 1, 1900. The default date system for Excel is 1900 date system. To verify this, we can use an inbuilt function in Excel, DATEVALUE. The DATEVALUE inbuilt function in Excel converts a date in its text format to its corresponding date serial number.
Following image shows Excel DATEVALUE function, converts the date January 1, 1900 into its Excel date serial number. The date string "01/01/1900" is converted into numeric value 1.
Following image shows Excel DATEVALUE function, converts the date January 2, 1900 into its Excel date serial number. The date string "02/01/1900" is converted into numeric value 2.
Following image shows Excel DATEVALUE function, converts the date December 5, 2021 into its Excel date serial number. The date string "05/12/2021" is converted into numeric value 44535. The meaning of the output of below Excel DATEVALUE function is that, 44535 days passed from January 1, 1900 till December 5, 2021.
1904 date system in Excel
The first day of 1904 date system is is considered as January 1, 1904. To verify this, we again use the inbuilt function in Excel, DATEVALUE. The DATEVALUE inbuilt function in Excel converts a date in its text format to its corresponding date serial number.
Before continuing, make sure that the date system is changed to 1904 date system. Click the following link to learn how to change the date system in Excel to 1904 date system.
Following image shows Excel DATEVALUE function, converts the date January 1, 1904 into its Excel date serial number. The date string "01/01/1904" is converted into numeric value 0.
Following image shows Excel DATEVALUE function, converts the date January 2, 1904 into its Excel date serial number. The date string "02/01/1904" is converted into numeric value 1.
Following image shows Excel DATEVALUE function, converts the date December 5, 2021 into its Excel date serial number. The date string "05/12/2021" is converted into numeric value 43073. The meaning of the output of below Excel DATEVALUE function is that, 43073 days passed from January 1, 1904 till December 5, 2021.