XL2000: Century Year Changes When Saved as Text File Format (230931)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q230931

SYMPTOMS

When you open a text file that you saved in Microsoft Excel, dates in the file may have an incorrect year. For example, if the date you saved was 1/1/2125, it may change to 1/1/2025 after you reopen the text file in Excel.

CAUSE

This problem occurs when you do the following:
  • You type a date in a cell with a year greater than 2000.

    -and-

  • You format the cell to show only a two-digit year.

    -and-

  • You save the file as any of the following text file formats:
    • Text (tab delimited) (*.txt)
    • Unicode text (*.txt)
    • CSV (comma delimited) (*.csv)
    • Formatted Text (space delimited) (*.prn)
    • Text (Macintosh) (*.txt)
    • Text (MS-DOS) (*.txt)
    • CSV (Macintosh) (*.CSV)
    • CSV (MS-DOS) (*.CSV)
    • DIF (data interchange format) (*.dif)

WORKAROUND

To save dates with the intended year in text files, format the cell to show four digits for the year:
  1. Select the cells that you want to format.
  2. On the Format menu, click Cells, and then click the Number tab.
  3. In the Category list, click Custom, and type mm/dd/yyyy in the Type box. Then, click OK.

MORE INFORMATION

When you save a file in text file format, Excel saves only the text and values as they are displayed in cells. All rows and all characters in each cell are saved for the active worksheet. All formatting, graphics, objects, and other worksheet contents are lost. Any formulas or dates are converted to the text or values that are displayed in the cell when you save the workbook. The underlying formulas and values are not saved.

When you open a text file, Excel sees only the two digits of the year. By default, Excel uses the following rule to determine the century for dates typed as a two-digit year:

00 through 29

Excel interprets the two-digit year values 00 through 29 as the years 2000-2029. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019.

30 through 99

Excel interprets the two-digit year values 30-99 as the years 1930 through 1999. For example, if you type the date 5/28/98, Excel assumes the date is May 28, 1998.

Note While you can change the way the system interprets two-digit dates under the Control Panel in Regional and Language Settings, Excel only uses that setting when you enter dates manually. If you import a text file or automate date entries by using Microsoft Visual Basic for Applications (VBA), the fixed 2029 rule is in effect.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

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

Modification Type:MinorLast Reviewed:6/26/2003
Keywords:kbpending kbprb KB230931
©2003 Microsoft Corporation. All rights reserved.