Article ID: 214391 - View products that this article applies to.
This article was previously published under Q214391
When you type a date using a two-digit year number (such as 98), Microsoft Excel uses specific rules to determine which century to use for the date. This article explains how Microsoft Excel determines the century.
When you type a date in a cell, if you omit the century digits from the year, Excel automatically determines which century to use for the date.
For example, if you type 7/5/98, Excel automatically uses the year 1998 and changes the date to 7/5/1998 in the formula bar.
The following sections explain the default rules that Excel uses.
Using the Regional Settings in Control PanelExcel first interprets dates according to the date ordering defined by the Short date style setting under Regional Settings in Control Panel, for example, M/d/yy.
If you are running Microsoft Windows 98 or later, you can use the When a two digit year is entered, interpret a year between setting under Regional Settings in Control Panel to determine the cutoff year for the century. The default value is 2029, but you can change this to any value between 99 and 9999.
Note You can change the When a two digit year is entered, interpret a year between setting to a value that is not compatible with Excel. If you enter an incompatible value, Excel will revert to the rules discussed in the "The 2029 Rule" section of this article.
To change the century cutoff date, follow these steps:
Note This will modify the way Excel interprets dates only when they are typed into a cell. If you import or programmatically enter a date, the following 2029 rule is always in effect.
Regional Settings Setting Date typed Date used ------------------------------------------ 2039 9/7/70 9/7/1970 2039 2/3/27 2/3/2027 2075 9/7/70 9/7/2070 2099 2/3/27 2/3/2027
The 2029 RuleBy default, Excel determines the century by using a cutoff year of 2029, which results in the following behavior:
Entering Dates That Contain Only Day/Month or Month/Year ComponentsSo 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, 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, 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.
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.
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
Article ID: 214391 - Last Review: January 24, 2007 - Revision: 6.3
Contact us for more help
Connect with Answer Desk for expert help.