Microsoft KB Archive/175362

= January 1 may appear as February 1 in a date in Excel 97 for Windows =

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.</li></ol>

The dates and values appear as follows: <pre class="fixed_text">  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

-

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

© Microsoft Corporation. All rights reserved.