MORE INFORMATION
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 dates
Excel 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 versions
To 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.
- On the Apple menu, click System Preferences.
- Click International.
- On the Formats tab, click Customize under Dates.
- Make the changes you want, and then click OK.
Note When you modify the Short Date format, the Region setting on the Formats tab changes to Custom.
Microsoft Excel 2001 on Mac OS 9 and earlier versions
To view or change the default date settings in Excel 2001 on Mac OS 9 and earlier versions, follow these steps:
- On the Apple menu, point to Control Panels, and then click Date & Time.
- Click Date Formats.
- In the Date Formats dialog box, make the changes that you want.
Note Any change that is made in the Date Formats dialog box causes the Region box to change to Custom. - Click OK to close the Date Formats dialog box, and then click the close box to close the Date & Time control panel.
Note You must restart Excel to apply your changes.
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 Preferences
To 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:
- On the Excel menu, click Preferences.
- On the Edit tab, click to select the Assume 21st century for two-digit years before check box , type the cutoff year that you want, and then click OK.
Note The default cutoff year is 30. However, you can type any cutoff year from 04 to 99.
To turn on and configure this setting in Excel 2001 on Mac OS 9, follow these steps:
- On the Edit menu, click Preferences.
- On the Edit tab, click to select the Assume 21st century for two-digit years before check box , type the cutoff year that you want, and then click OK.
Note The default cutoff year is 30. However, you can type any cutoff year from 04 to 99.
The default value of 30 means that two-digit years that are less than 30 are assumed to be in the 21st century. For example, if you type
1/1/29, Excel interprets this as 1/1/2029. For more information, see "The 2029 rule" section.
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 rule
By 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:
- Dates in the inclusive range from January 1, 1904 (1/1/1904) to December 31, 9999 (12/31/9999) are valid.
- When you type a date that uses a two-digit year, Excel uses
the following centuries:
Two-digit
year typed Century used
---------------------------------
00-29 21st (year 2000)
30-99 20th (year 1900)
For example, when you type the following dates, Excel interprets these as follows:
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.
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, 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.
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 the
International window or the
Date & Time control panel.