Article ID: 214330 - View products that this article applies to.
This article was previously published under Q214330
For a Microsoft Excel 2000 version of this article, see 180162
Microsoft Excel supports two different date systems. These systems are the 1900 date system and the 1904 date system. This article describes the two date systems and the problems that you may encounter when you use workbooks that use different date systems.
The 1900 Date SystemIn 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 number that represents the number of elapsed days since January 1, 1900. For example, if you enter July 5, 1998, Excel converts the date to the serial number 35981.
By default, Microsoft Excel for Windows uses the 1900 date system. The 1900 date system enables better compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.
The 1904 Date SystemIn 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 number that represents the number of elapsed days since January 1, 1904. For example, if you enter July 5, 1998, Excel converts the date to the 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 January 1, 1904 were not supported. This design was intended to prevent problems related to the fact that 1900 was not a leap year. If you switch to the 1900 date system, Excel for the Macintosh does support dates as early as January 1, 1900.
The Difference Between the Date SystemsBecause the two date systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, 1998 can have two different serial numbers, as follows.
The difference between the two date systems is 1,462 days; that is, the serial number of a date in the 1900 Date System is always 1,462 days bigger 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).
Serial number Date system of July 5, 1998 ---------------------------------- 1900 date system 35981 1904 date system 34519
Setting the Date System for a WorkbookIn Excel, each workbook can have its own date system setting, even if multiple workbooks are open.
To set the date system for a workbook in Microsoft Office Excel 2003 and in earlier versions of Excel, follow these steps:
To set the date system for a workbook in Microsoft Office Excel 2007, follow these steps:
Problems Linking and Copying Dates Between WorkbooksIf two workbooks use different date systems, you may encounter problems when you link or copy dates between workbooks. Specifically, the dates may be shifted by four years and one day.
To see an example of this behavior, follow these steps:
Correcting Shifted DatesIf you link from or copy dates between workbooks, or if you change the date system for a workbook that already contains dates, the dates may be shifted by four years and one day. You can correct shifted dates by following these steps:
If you are using a formula to link to a date in another workbook, and if the date returned by the formula is incorrect because the workbooks use different date systems, modify the formula to return the correct date. For example, use the following formulas:
In these formulas, 1,462 is added or deleted from the date value.
For more information about how to use the 1900 date system and the 1904 date system in Excel, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/214348/ )Chart axis may be four years early after you format scale
(http://support.microsoft.com/kb/214365/ )DATE function may return #NUM! error when year is 0-3
(http://support.microsoft.com/kb/213593/ )Date returned in a macro is four years too early
(http://support.microsoft.com/kb/214239/ )Sheet protection does not disable options settings
(http://support.microsoft.com/kb/214058/ )Days of the week before March 1, 1900, are incorrect in Excel
Article ID: 214330 - Last Review: March 5, 2013 - Revision: 12.0
Contact us for more help