Article ID: 175362
Article Last Modified on 7/28/2004
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q175362
SYMPTOMS
When you enter or fill dates in a worksheet in Microsoft Excel 97, a date that should appear as January 1 may instead appear as February 1.
CAUSE
This problem occurs if the year of a date is the first leap year after a century year that is not a leap year. Dates in the following years may be affected: 2104, 2204, 2304, 2504, 2604, 2704, 2904, 3004, 3104, 3304, and so on.
For example, if you enter the date 1/1/2104 (January 1, 2104), the date appears in the cell as 2/1/2104 (February 1, 2104).
NOTE: This problem does not occur in earlier versions of Microsoft Excel. Earlier versions of Microsoft Excel do not support dates after the year 2078.
RESOLUTION
To resolve this problem, download and install the Microsoft Office Service Release 2 (SR-2) Patch. For information about obtaining the Office 97 Service Release 2, or for general information about SR-2, click the article numbers below to view the articles in the Microsoft Knowledge Base:
151261 OFF97: How to Obtain and Install MS Office 97 SR-2
192874 OFF97: An Overview of Microsoft Office 97 SR-2
For information about Year 2000 issues and SR-2, click the article number below to view the article in the Microsoft Knowledge Base:
233537 OFF97: Year 2000 Related Problems Corrected in Office 97 SR-2
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
Although the date may appear as February 1, it actually has the correct serial value for January 1. This problem affects only the formatting of the date, not the underlying value of the date.
If you want to display a date that exhibits this behavior, precede the date with an apostrophe (') when you enter it. For example, enter the following in cell A1:
A1: '1/1/2104
To make the date appear as a normal date, align the date to the right.
If you want to use the date that is preceded by an apostrophe in a calculation, use the DATEVALUE function, as in the following example:
B1: =DATEVALUE(A1)
For additional information about how to determine if a year is a leap year, click the following article number to view the article in the Microsoft Knowledge Base:
214019 XL: Method to determine whether a year is a leap year
The problem described in this article occurs only when you enter or fill a date that occurs on January 1 in a year that is the first leap year after a century year that is NOT a leap year. For example, the year 2104 is the first leap year after 2100, a century year that is not a leap year.
NOTE: No other dates are affected by this problem, including other dates in January (for example, January 10, 2104).
For a demonstration of this problem, follow these steps:
- In Microsoft Excel 97, create a new workbook.
Enter the following dates and formulas in Sheet1 of the workbook:
A1: 12/29/2103 B1: =VALUE(A1) A2: 12/30/2103 B2: =VALUE(A2)- Select cells A1:A2. On the Format menu, click Cells. Click the Number tab. In the Category list, click Custom. In the Type box, type "m/d/yyyy" (without the quotation marks). Click OK.
- Select cells A1:B2. Drag the fill handle (at the lower-right corner of cell B2) down until the range A1:B10 is selected. Release the mouse button.
The dates and values appear as follows:
A1: 12/29/2103 B1: 74508
A2: 12/30/2103 B2: 74509
A3: 12/31/2103 B3: 74510
A4: 2/1/2104 B4: 74511
A5: 1/2/2104 B5: 74512
A6: 1/3/2104 B6: 74513
A7: 1/4/2104 B7: 74514
A8: 1/5/2104 B8: 74515
A9: 1/6/2104 B9: 74516
A10: 1/7/2104 B10: 74517
Note that the date in cell A4 appears as February 1, 2104 rather than January 1, 2104. The dates before and after this date appear correctly.
The value in cell B4 is correct: this example shows that the serial value of the date in cell A4 is correct and that the problem exists only in the formatting of the date in cell A4.
Additional query words: XL97 leap-year leapyear fill down up autofill y2k year2000 new years day
Keywords: kbbug KB175362
