Serial Number and Serial Date in Excel

Changing the date system for dates and times in Excel screenshot

Ted French

The serial number or serial date is the number that Excel uses in calculating dates and times entered into a worksheet, either manually or as a result of formulas involving date calculations.

Excel reads the computer's system clock in order to keep track of the amount of time that has elapsed since the date system's start date.

Two Possible Date Systems

By default, all versions of Excel that run on the Windows operating system store the date as a value representing the number of full days since midnight January 1, 1900, plus the number of hours, minutes, and seconds for the current day.

Versions of Excel that run on Macintosh computers default to one of two date systems.

  • Since Excel 2011, the default date system is the 1900 date system which guarantees date compatibility with Excel for Windows.
  • For Excel 2008 and earlier versions of Excel (Macintosh), the default date system begins on January 1, 1904, and is referred to as the 1904 date system.

All versions of Excel support both date systems and changing from one system to the other is easily done using the program options.

Serial Number Examples

In the 1900 system, the serial number 1 represents January 1, 1900, 12:00:00 a.m. while the number 0 represents the fictitious date January 0, 1900.

In the 1904 system, the serial number 1 represents January 2, 1904, while the number 0 represents January 1, 1904, 12:00:00 a.m.

Times Stored as Decimals

Times in both systems are stored as decimal numbers between 0.0 and 0.99999, where

  • 0.0 is 00:00:00 (hours:minutes:seconds);
  • 0.5 is 12:00:00 (12 p.m.);
  • 0.99999 is 23:59:59.

To show dates and times in the same cell in a worksheet, combine the integer and decimal portions of a number.

For example, in the 1900 system, 12 p.m. on January 1, 2016, is serial number 42370.5 because it is 42370 and one-half days (times are stored as fractions of a full day) after January 1, 1900.

Similarly, in the 1904 system, the number 40908.5 represents 12 p.m. on January 1, 2016.

Serial Number Uses

Many, if not most, projects that use Excel for data storage and calculations, use dates and times in some way. For example:

  • A long-term project may need to count the number of days between current and past dates or determine a date that is so many days in the future using functions such as NETWORKDAYS and EDATE.
  • Timesheets calculate the elapsed time between and start and end times, as well as hours, are overtime as necessary using formulas that add or subtract dates and times.
  • Timestamping a worksheet with the current date and time can be done with keyboard shortcuts that read the current serial number.
  • Updating the displayed date and/or time whenever a worksheet is opened or recalculated with the NOW and TODAY functions.

Why Two Date Systems?

Briefly, PC versions of Excel (Windows and DOS operating systems) initially used the 1900 date system for the sake of compatibility with Lotus 1-2-3, the most popular spreadsheet program at the time.

The problem with this is that when Lotus 1-2-3 was created, the year 1900 was programmed in as a leap year, when in fact it was not. As a result, additional programming steps needed to be taken to correct the error.

Current versions of Excel keep the 1900 date system for the sake of compatibility with worksheets created in previous versions of the program.

Since there was no Macintosh version of Lotus 1-2-3, initial versions of Excel for Macintosh did not need to be concerned with compatibility issues and the 1904 date system was chosen to avoid the programming problems related to the 1900 non-leap-year issue.

On the other hand, it did create a compatibility issue between worksheets created in Excel for Windows and Excel for the Mac, which is why all new versions of Excel use the 1900 date system.

Changing the Default Date System

Only one date system can be used per workbook. If the date system for a workbook that already contains dates is changed, those dates shift by four years and one day due to the time difference between the two date systems mentioned above.

To set the date system for a workbook in Excel 2010 and later versions:

  1. Open or switch to the workbook to be changed

  2. Click on the File tab to open the File menu

  3. Click on Options in the menu to open the Excel Options dialog box

  4. Click on Advanced in the left-hand panel of the dialog box

  5. Under the When calculating this workbook section in the right-hand panel, select or clear the Use 1904 date system check box

  6. Click OK to close the dialog box and return to the workbook

To set the date system for a workbook in Excel 2007:

  1. Open or switch to the workbook to be changed

  2. Click on the Office Button to open the Office menu

  3. Click on Options in the menu to open the Excel Options dialog box

  4. Click on Advanced in the left-hand panel of the dialog box

  5. Under the When calculating this workbook section in the right-hand panel, select or clear the Use 1904 date system check box

  6. Click OK to close the dialog box and return to the workbook