XL97: Four-Digit Years May Display Only Two Digits

ID: Q236999

The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you open a Microsoft Excel workbook, cells that you had previously formatted to display four-digit years may now display two-digit years. This behavior occurs even if you had previously used the Date Fix Wizard to change all date formats in your workbook to display four-digit years.


CAUSE

This behavior occurs when either of the following conditions is true:

  • The Short date style setting on the Date tab (under Regional Settings in Control Panel) has changed.

    -or-

  • Another user who has a different Short date style setting has saved changes to the workbook.
When you edit a workbook on a different computer with a different Short date style setting, Excel may change the format that it uses to display date values. This allows Excel to seamlessly convert date formats when you share your workbooks with people who are using different regional settings. This behavior is by design of Microsoft Excel.


WORKAROUND

To prevent this behavior from impacting the use of your workbooks, everyone who opens your workbook must have the same Short date style setting under Regional Settings in Control Panel.


MORE INFORMATION

The following steps illustrate how to reproduce this behavior.

NOTE: These steps assume that the current Short date style setting on your computer is M/d/yy.

  1. Save and close any open workbooks and then open a new workbook.


  2. Type the following date into cell A1:
    9/7/1999
    By default, the date is displayed as 9/7/99.


  3. Select cell A1 and then click Cells on the Format menu. Click the Number tab and in the Category list, click Custom. In the Type box, type m/d/yyyy and then click OK.

    The date in cell A1 is now displayed as 9/7/1999.


  4. Save, and then close the file.


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


  6. Double-click the Regional Settings icon.


  7. On the Date tab, click M/d/yyyy in the Short date style list and then click OK.


  8. Switch to Excel. Open the file that you saved in step 4.

    The date in cell A1 is still displayed as 9/7/1999.


  9. Save, and then close the file.


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


  11. Double-click the Regional Settings icon.


  12. On the Date tab, click M/d/yy in the Short date style list and then click OK.

    The Short date style is now back to its default value.


  13. Switch to Excel.


  14. Open the file that you last saved in step 9.

    The date in cell A1 is now displayed as 9/7/99.



REFERENCES

For additional information about the Date Fix Wizard, please click the article number below to view the article in the Microsoft Knowledge Base:

Q176943 XL: Year 2000 Wizards for Microsoft Excel 97 and 2000 Available for Download

Additional query words: XL97

Keywords : kbdta kb2000 xlformat xlwiz
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: August 24, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.