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

Last reviewed: March 19, 1998
Article ID: Q164406
The information in this article applies to:
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0
  • Microsoft Excel for OS/2, versions 2.x, 3.0
  • Microsoft Excel 98 Macintosh Edition
  • Microsoft Excel for the Power Macintosh, version 5.0
  • Microsoft Excel for the Macintosh, versions 3.x, 4.x, 5.0

SUMMARY

When you type a date using a two-digit year number (for example, 98) in Microsoft Excel, 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" (without the quotation marks), 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 are used by different versions of Microsoft Excel.

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 using 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 prior to 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" (without the quotation marks).

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.

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

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

          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
    
    
  • If you want to type a date that is after December 31, 1999, you MUST type the full four-digit year.

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" (without quotation marks) 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.


Additional query words: 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
Keywords : xlformula kb2000
Version : MACINTOSH:3.0,4.0,5.0,98;WINDOWS:2.0,3.0,4.0,5.0,7.0,97
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 19, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.