Microsoft KB Archive/181194

= MacXL: Wrong Date Returned When You Enter Month and Digits =

Article ID: 181194

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q181194





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

For example, when you enter the following dates, Excel displays the following:   Date entered       Date that appears

Dec 1             December 1, * Dec 31            December 31, * Dec 32            December 1, 1932 December 99       December 1, 1999 =YEAR("Dec 2")    * =YEAR("Dec 33")   1933

* where is the current year in four-digit format.



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/1998", 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.



MORE INFORMATION
By default, Excel interprets two-digit years less than 30 as being in the twenty-first century (29 becomes 2129), and years 30 and greater as being in the twentieth century (30 becomes 1930).

In Excel 2001, you can change this default behavior with the Assume 21st century for two-digit years before setting. (On the Edit menu, click Preferences, and then click the Edit tab.) For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

302768 XL2001: How Microsoft Excel Works with Two-Digit Year Numbers

Additional query words: XL2001 XL98 y2k year2000 year 2000 2,000 february 2029

Keywords: kbbug kbpending KB181194

-

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

© Microsoft Corporation. All rights reserved.