You are currently offline, waiting for your internet to reconnect

XL: The 1900 Date System vs. the 1904 Date System

This article was previously published under Q180162
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
Microsoft Excel supports two different date systems: the 1900 date systemand the 1904 date system. This article describes the two date systems andthe problems that you may encounter when you use workbooks that usedifferent date systems.
MORE INFORMATION

The 1900 Date System

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial numberthat represents the number of elapsed days since January 1, 1900. Forexample, if you enter July 5, 1998, Microsoft Excel converts the date tothe serial number 35981.

By default, Microsoft Excel for Windows and Microsoft Excel for Windows NTuse the 1900 date system. The 1900 date system allows greater compatibilitybetween Microsoft Excel and other spreadsheet programs, such as Lotus1-2-3, that are designed to run under MS-DOS or Microsoft Windows.

The 1904 Date System

In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial numberthat represents the number of elapsed days since January 1, 1904. Forexample, if you enter July 5, 1998, Microsoft Excel converts the date tothe serial number 34519.

By default, Microsoft Excel for the Macintosh uses the 1904 date system.Because of the design of early Macintosh computers, dates before January1, 1904 were not supported; this design was intended to prevent problemsrelated to the fact that 1900 was not a leap year. Note that if you switchto the 1900 date system, Microsoft Excel for the Macintosh does supportdates as early as January 1, 1900.

The Difference Between the Date Systems

Because the two Date Systems use different starting days, the same date isrepresented by different serial numbers in each date system. For example,July 5, 1998 can have two different serial numbers.
                      Serial number   Date system        of July 5, 1998   ----------------------------------   1900 date system   35981   1904 date system   34519				
The difference between the two date systems is 1,462 days; that is, theserial number of a date in the 1900 Date System is always 1,462 daysgreater than the serial number of the same date in the 1904 date system.1,462 days is equal to four years and one day (including one leap day).

Setting the Date System for a Workbook

In Microsoft Excel, each workbook can have its own date system setting,even if multiple workbooks are open. You can set the date system for aworkbook by following these steps:
  1. Open or switch to the workbook.
  2. On the Tools menu (or Edit menu in Excel 2001 for Mac), click Options or Preferences. Then, click the Calculation tab.
  3. To use the 1900 date system in the workbook, click to clear the 1904 Date System check box. To use the 1904 date system in the workbook, click to select the check box.
  4. Click OK.
Note that if you change the date system for a workbook that alreadycontains dates, the dates shift by four years and one day. For informationabout correcting shifted dates, see the "Correcting Shifted Dates" sectionin this article.

Problems Linking and Copying Dates Between Workbooks

If two workbooks use different date systems, you may encounter problemswhen you link or copy dates between workbooks. Specifically, the dates maybe shifted by four years and one day.

To see an example of this behavior, follow these steps:
  1. In Microsoft Excel, create two new workbooks (Book1 and Book2).
  2. Use the steps in the "Setting the Date System for a Workbook" section to use the 1900 date system in Book 1. Use the 1904 date system in Book2.
  3. In Book1, enter the date July 5, 1998.
  4. Select the cell that contains the date, and click Copy on the Edit menu.
  5. Switch to Book2, select a cell, and click Paste on the Edit menu.

    The date is pasted as July 6, 2002. Note that the date is four years and one day later than the date in step 3 because Book2 uses the 1904 date system.
  6. In Book2, enter the date July 5, 1998. Select the cell that contains the date and click Copy on the Edit menu.
  7. Switch to Book1, select a cell, and click Paste on the Edit menu.
The date is pasted as July 4, 1994. It has been shifted down by four yearsand one day because Book1 uses the 1900 date system.

Correcting Shifted Dates

If you link from or copy dates between workbooks, or if you change the datesystem for a workbook that already contains dates, the dates may be shiftedby four years and one day. You can correct shifted dates by following thesesteps:
  1. In an empty cell, enter the value 1462.
  2. Select the cell. On the Edit menu, click Copy.
  3. Select the cells that contain the shifted dates. On the Edit menu, click Paste Special.
  4. In the Paste Special dialog box, click Values. Then, select either of the following option buttons.
       Select this     If   --------------------------------------------------------------------   Add             The dates must be shifted up by four years and one                   day.   Subtract        The dates must be shifted down by four years and one                   day.					
  5. Click OK.
Repeat these steps until all of the shifted dates have been corrected.

If you are using a formula to link to a date in another workbook, and ifthe date returned by the formula is incorrect because the workbooks usedifferent date systems, modify the formula to return the correct date, forexample:
   =[Book2]Sheet1!$A$1+1462				
   =[Book1]Sheet1!$A$1-1462				
In the formulas, 1,462 is added or deleted from the date value.

More Information in the Microsoft Knowledge Base

The Microsoft Knowledge Base contains several other articles that haveinformation about using the 1900 date system and the 1904 date system inMicrosoft Excel. These articles are listed as follows:
274277 MacXL: Chart Axis May Be Four Years Early After You Format Scale

214318 XL2000: Chart Axis May Be Four Years Early After You Format Scale

177172 XL97: Chart Axis May Be Four Years Early After You Format Scale

175753 XL: DATE Function May Return #NUM! Error When Year Is 0-3

157035 XL: Date Returned in a Macro Is Four Years Too Early

156987 XL: Sheet Protection Does Not Disable Options Settings
XL2001 XL98 XL97 y2k year2000 1900 1901 1902 1903 1904
Properties

Article ID: 180162 - Last Review: 12/05/2015 08:21:03 - Revision: 2.3

  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 95a
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 5.0c
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
  • Microsoft Excel 5.0 for Macintosh
  • Microsoft Excel 5.0a for Macintosh
  • kbnosurvey kbarchive KB180162
Feedback
cookies
  • Trademarks
  • © 2015 Microsoft