XL2000: How Microsoft Excel Works with Two-Digit Year Numbers

ID: Q214391

The information in this article applies to:

Microsoft Excel 2000

SUMMARY

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 Excel 2000 determines the century.

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 2000 uses.

Using the Regional Settings in Control Panel

Excel 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, 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:

Click Start, point to Settings, and then click Control Panel.

Double-click the Regional Settings icon.

Click the Date tab.

In the When a two digit year is entered, interpret a year between box, type the cutoff year you want and then click OK.

The following table illustrates the effect that various cutoff years will have when you type a two-digit year in Excel 2000:


   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 Rule

By default, Excel 2000 determines the century by using a cutoff year of 2029, which results in the following behavior:

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, 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, 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.


   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.

REFERENCES

For more information about how earlier versions of Excel work with two-digit year numbers, click the article number below to view the article in the Microsoft Knowledge Base:

Q164406 XL: How Microsoft Excel Works with Two-Digit Year Numbers

For more information about how Microsoft products are affected by year 2000 (Y2K) issues, please see the following Microsoft World Wide Web site:

http://www.microsoft.com/year2000/

Additional query words: 1919 1920 1929 1930 2019 2020 2029 2030 y2k year2000 year 2000 XL2000

Keywords : kb2000 xlformula
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto