- How dates are calculated in Excel spreadsheets
- Two possible date systems
- Serial Number Examples
- Time is stored in decimals
- Using the serial number
- Changing the Default Date System
- Why two date systems?
How dates are calculated in Excel spreadsheets
The serial number or serial date is the number that Excel uses to calculate the dates and times entered in the worksheet. The serial number is either calculated manually or using formulas with date calculations. Excel reads the computer’s system clock to track the amount of time that has passed since the date system’s start date.
Remark † The information in this guide applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and Excel for Mac.
Two possible date systems
By default, Windows versions of Excel store a date as a value that represents the number of full days since midnight on January 1, 1900, plus the number of hours, minutes, and seconds for the current day.
By default, versions of Excel that run on Macintosh computers use one of the following two date systems:
Excel for Mac versions 2019, 2016, and 2011: The default date system is 1900, which ensures date compatibility with Excel for Windows.
Excel 2008 and earlier: The default date system starts on January 1, 1904 and is called the 1904 date system.
All versions of Excel support both date systems and it is possible to switch from one system to the other.
Serial Number Examples
In the 1900 system, serial number 1 represents January 1, 1900, 00:00 and serial number 0 represents the fictitious date January 0, 1900.
In the 1904 system, serial number 1 stands for January 2, 1904, and serial number 0 for January 1, 1904, 00:00.
Time is stored in decimals
The time is stored in both systems as decimal numbers from 0.0 to 0.99999, where:
0.0 – 00:00:00 (hours: minutes: seconds)
0.5 – 12:00:00 (12:00)
0.9999 – 23:59:59
To display dates and times in the same cell on a worksheet, combine the integer and decimal parts of a number.
For example, in the 1900 system, 12:00 noon. January 1, 2016, is serial number 42370.5 because it is 42370 and a half day (time is stored in fractions of a whole day) after January 1, 1900. Similarly, in the 1904 system, the number 40908.5 stands for 12 noon. January 1, 2016
Using the serial number
Many projects that use Excel for data storage and calculations use dates and times in one way or another. For instance:
A long-term project that counts the number of days between current and past dates using the NETWORKDAYS function.
Loan calculation that determines a future date using the EDATE function.
Timesheets that calculate the elapsed time between start and end times, and hours and overtime as needed using formulas that add or subtract dates and times.
Timestamp on a worksheet showing the current date and time using keyboard shortcuts that read the current serial number.
Updates the date and time displayed when the worksheet is opened or recalculated using the NOW and TODAY functions.
Only one date system can be used per workbook. If the date system changes for a workbook that contains dates, those dates are shifted four years and one day because of the time difference between the two date systems.
Changing the Default Date System
To set the date system for a workbook in Excel running on a Windows PC:
Open the workbook for editing.
Select File † Except for Excel 2007 where you click a button Office †
Select parameters: to open the Excel Options dialog box.
Select Additionally in the left pane of the dialog box.
In the “Calculate this book” section, check or uncheck the box Use the 1904 date system †
Select Okay to close the dialog box and return to the workbook.
To set the date system for a workbook in Excel for Mac:
Open the workbook for editing.
Select menu excel †
Select Settings to open the Excel Preferences dialog box.
In the “Formulas and List” section, select Payment.
In the “When calculating workbooks” section, check or uncheck the box Use the 1904 date system †
Close the Excel Preferences dialog box.
Why two date systems?
The PC versions of Excel (Windows and DOS operating systems) originally used the 1900 date system for compatibility with Lotus 1-2-3, the most popular spreadsheet program of the time.
The problem is that when creating Lotus 1-2-3 1900 was programmed as a leap year, but in fact it wasn’t. As a result, additional programming steps were required to correct the error. Current versions of Excel retain the 1900 date system for compatibility with tables created in earlier versions of the program.
Since there was no Macintosh version of Lotus 1-2-3, the early Macintosh versions of Excel didn’t have to worry about compatibility issues. The 1904 date system was chosen to avoid programming problems associated with the 1900 non-leap year problem.
On the other hand, this caused a compatibility issue between worksheets created in Excel for Windows and Excel for Mac. This is why all newer versions of Excel use the 1900 date system.