XL2000: Century Year Changes When Saved as Text File Format

• Microsoft Excel 2000

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.

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

Additional query words: XL2000 Y2K

Keywords : kbdta kb2000 xlformat
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb