XL: Wrong Date Returned When You Enter Month and Digits

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, version 5.0
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel, if you enter a month name that is spelled out or abbreviated and is followed by a space and one or two digits, the date that appears in the cell may be different than the date you expect.

For example, when you enter the following dates, Excel displays the dates in the following table.

   Date You Enter   The Date That Appears
   --------------------------------------

   Dec 1            December 1, 1998
   Dec 31           December 31, 1998
   Dec 32           December 1, 1932
   December 99      December 1, 1999

CAUSE

When you enter a month and a one or two digit number, Microsoft Excel assumes that the digits represent the day of the month and that the date is in the current year. For example, "Dec 31" translates into December 31 in the current year.

However, if the digits exceed the number of days in the month, Microsoft Excel converts the digits to a year and assumes that the date is on the first of the month. For example, "Dec 32" translates into December 1, 1932.

RESOLUTION

To prevent problems from occurring when you enter dates, use the following guidelines:

• When possible, enter dates more fully.

  For example, instead of typing "Dec 31," type "Dec 31, 1998," "Dec 31, 98," "12/31/98," or "31-Dec-1998." This prevents Microsoft Excel from misinterpreting the dates.

• Format cells that contain dates so that they display not only the month and day, but also the year.

  This helps you to more easily tell the difference between "Dec-31" and "31-Dec."

• If you are importing a text file that contains dates, you can use the Text Import Wizard to apply proper date formatting to dates before you import them into Microsoft Excel.

  Note that you should also check dates after you import them to make sure that they appear correctly.

STATUS

This behavior is by design of the versions of Microsoft Excel listed above.