Article ID: 302768 - View products that this article applies to.
This article was previously published under Q302768
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 the Microsoft Excel versions listed in the Applies To section determine 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.
Changing how Excel displays datesExcel first interprets date according to the preferences that are defined in the Short Date setting. For example, if you select Month/Date/Year in the Short Date settings, Excel will display July 5, 1998 as 7/5/98.
Microsoft Excel X for Mac and later versionsTo view the Short Date settings that are used by Microsoft Excel X for Mac and later versions on Mac OS X, follow these steps. You can also change how you want Excel to display numbers.
Microsoft Excel 2001 on Mac OS 9 and earlier versionsTo view or change the default date settings in Excel 2001 on Mac OS 9 and earlier versions, follow these steps:
Changes that are made in the International window or in the Date & Time control panel may change date formats in cells that have existing entries, the default date format in Excel, and the available date formats in the Format Cells dialog box.
Microsoft Excel uses the date formats in the International window or the Date & Time control panel to determine what number formats and separators to use (such as "/" or "-"). These settings affect the way Microsoft Excel displays and uses dates and times.
If you type a date in a cell and then do not click Cells on the Format menu in Excel to choose a number format, Excel uses the Short Date format that is defined in the International window on Mac OS X or the Date & Time control panel in Mac OS 9. When you change the Short Date format, Excel automatically changes the date format in the appropriate cells.
Note One useful change that you can make when you modify the Short Date setting is to click to select the Show century check box. This setting makes the short date in Excel use the full four-digit year ("yyyy" format) by default.
Note Some changes that you make when you modify the Short Date settings may be incompatible with Excel, and they may create date formats that Excel cannot use. If you experience problems with Excel dates, open the International window on Mac OS X or the Date & Time control panel on Mac OS 9 or earlier versions, and then check the Region setting. If the Region setting is Custom, set the date formats back to their defaults by selecting the appropriate region, such as U.S..
Using the Edit tab in Excel PreferencesTo change how Excel interprets the century when you enter a 2-digit year, use the Assume 21st century for two-digit years before setting.
To turn on and configure this setting in Excel X and later versions on Mac OS X, follow these steps:
The following table illustrates the effect that various cutoff years will have when you type a two-digit year in Excel 2001:
Preferences Setting Date typed Date used -------------------------------------- 39 9/7/70 9/7/1970 39 2/3/27 2/3/2027 75 9/7/70 9/7/2070 99 2/3/27 2/3/2027
The 2029 ruleBy default, the Excel versions that are listed in the "Applies To" section determine the century by using a cutoff year of 2029. This 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 the International window or the Date & Time 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: 302768 - Last Review: October 6, 2011 - Revision: 4.0
Contact us for more help