Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/175362

From BetaArchive Wiki
Knowledge Base


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:

  1. In Microsoft Excel 97, create a new workbook.
  2. 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)
                        
  3. 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.
  4. 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