Microsoft KB Archive/230931

= XL2000: Century Year Changes When Saved as Text File Format =

Article ID: 230931

Article Last Modified on 6/26/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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)</li> Text (Macintosh) (*.txt)</li> Text (MS-DOS) (*.txt)</li> CSV (Macintosh) (*.CSV)</li> CSV (MS-DOS) (*.CSV)</li> DIF (data interchange format) (*.dif)</li></ul> </li></ul>

<div class="workaround_section">

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.

<div class="moreinformation_section">

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

Additional query words: XL2000 Y2K

Keywords: kbprb kbpending KB230931

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.