Microsoft KB Archive/164406

= How Microsoft Excel Works with Two-Digit Year Numbers =

PSS ID Number: 164406

Article Last Modified on 6/29/2004

-

The information in this article applies to:


 * Microsoft Excel 97 for Windows
 * Microsoft Excel for Windows 95 7.0
 * Microsoft Excel for Windows 5.0
 * Microsoft Excel 98 Macintosh Edition

-



This article was previously published under Q164406



SUMMARY
When you type a date using a two-digit year number (for example, 98), Microsoft Excel uses specific rules to determine which century to use for the date. This article explains how different versions of Microsoft Excel determine the century year.



MORE INFORMATION
When you type a date in a cell, you may want to omit the century digits from the year. If you do this, Microsoft Excel automatically determines the century to use for the date.

For example, if you type 7/5/98, Microsoft Excel automatically uses the year 1998 and changes the date to 7/5/1998 in the formula bar.

The following sections explain the rules that the different versions of Microsoft Excel use.

Microsoft Excel 97 for Windows and Excel 98 Macintosh Edition
Excel 97 and Excel 98 Macintosh Edition determine the century year by using the following rules:  Dates in the inclusive range from January 1, 1900 (1/1/1900) to December 31, 9999 (12/31/9999) are valid.  When you type a date that uses a two-digit year, Microsoft Excel uses the following centuries.      Two-digit        Century used year typed -

00-29           21st (year 2000) 30-99           20th (year 1900) For example, when you type the following dates, Microsoft Excel uses the following dates.      Date typed      Date used -

7/4/00      7/4/2000          1/1/10       1/1/2010        12/31/29     12/31/2029          1/1/30       1/1/1930          7/5/98       7/5/1998        12/31/99     12/31/1999                     If you want to type a date that is before January 1, 1930, or after December 31, 2029, you MUST type the full four-digit year. For example, to use the date July 4, 2076, type 7/4/2076 .

Microsoft Excel Versions 3.x, 4.x, 5.x, 7.x
 Dates in the inclusive range from January 1, 1900 (1/1/1900) to December 31, 2078 (12/31/2078) are valid.  When you type a date using a two-digit year, Microsoft Excel uses the following centuries. <pre class="fixed_text">     Two-digit        Century used year typed -

00-19           21st (year 2000) 20-99           20th (year 1900) For example, when you type the following dates, Microsoft Excel uses the following dates. <pre class="fixed_text">     Date typed      Date used -

7/4/00      7/4/2000          1/1/10       1/1/2010        12/31/19     12/31/2019          1/1/20       1/1/1920          7/5/98       7/5/1998        12/31/99     12/31/1999                    </li> If you want to type a date that is prior to January 1, 1920, or after December 31, 2019, you MUST type the full four-digit year.</li></ul>

Microsoft Excel 2.x
 Dates in the inclusive range from January 1, 1900 (1/1/1900) to December 31, 2078 (12/31/2078) are valid.</li>  When you type a date using a two-digit year, Microsoft Excel 2.x ALWAYS assumes that you want the date to be in the 20th century.

For example, when you type the following dates, Microsoft Excel uses the following dates. <pre class="fixed_text">     Date typed      Date used -

7/4/00      7/4/1900          1/1/10       1/1/1910        12/31/19     12/31/1919          1/1/20       1/1/1920          7/5/98       7/5/1998        12/31/99     12/31/1999                    </li> If you want to type a date that is after December 31, 1999, you MUST type the full four-digit year.</li></ul>

Differences in Behavior
In Excel 97 and Excel 98 Macintosh Edition, the two-digit "boundary" year for the 21st century is ten years later than the boundary year in earlier versions of Microsoft Excel. This change makes it more convenient to use two-digit year numbers, but it causes two-digit year dates from 20 to 29 to work differently in Excel 97 and Excel 98 Macintosh Edition.

For example, if you type 11/17/25 in Microsoft Excel 97 or Excel 98 Macintosh Edition, the date appears as 11/17/2025; in earlier versions of Microsoft Excel, the date appears as 11/17/1925.

Note that Microsoft Excel 2.x is the exception to this rule. In Microsoft Excel 2.x, there is no two-digit boundary year; all two-digit years are assumed to be in the 20th century.

If dates appear to be using the incorrect century, check the dates in the formula bar, or format the dates with the four-digit year format.

Entering Dates That Contain Only Day/Month or Month/Year Components
So far, this article has discussed how Excel interprets three-part date entries that contain month, day, and year components. It is possible to enter a two-part date that contains only the day and month, or the month and year components of the date. Two-part dates are inherently ambiguous and should be avoided if possible. This section discusses how Excel handles date entries that contain only two parts.

When you enter a date that contains only two of the three date components, Excel assumes that the date is in the form of Day/Month or Month/Year. Excel first attempts to resolve the entry as a Day/Month entry in the current year. If it cannot resolve the entry in the Day/Month form, then Excel attempts to resolve the entry in the Month/Year form, using the first day of that month. If it cannot resolve the entry in the Month/Year form, then Excel interprets the entry as text.

The following table illustrates how Excel interprets various date entries that contain only two of the three date components.

NOTE: This table assumes that the current year is 1999.

<pre class="fixed_text">  Entry      Resolution -     --

12/01     12/1/1999   12/99      12/1/1999   11/95      11/1/1995   13/99      13/99 (text) 1/30      1/30/1999   1/99       1/1/1999   12/28      12/28/1999 NOTE: This table illustrates how Excel stores the date, not how the date is displayed in the cell. The display format of the date varies according to the date formats that have been applied to the cell, and the current settings under Regional Settings in Control Panel.

Additional query words: 2 digit 4 digits interpets interprets 1919 1920 1929 1930 2019 2020 2029 2030 XL98 XL97 XL7 XL5 XL4 XL3 XL2 3.0 2.2 2.1d 2.1c 2.1 2.0 y2k year2000 year 2000 XL

Keywords: kbinfo KB164406

Technology: kbExcel500 kbExcel95 kbExcel95Search kbExcel97Search kbExcel98 kbExcel98Search kbExcelMacsearch kbExcelSearch kbExcelWinSearch kbHWMAC kbOSMAC kbZNotKeyword3

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© 2004 Microsoft Corporation. All rights reserved.