XL97: January 1 May Appear as February 1 in a Date

ID: Q175362

The information in this article applies to:

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:

Q151261 OFF97: How to Obtain and Install MS Office 97 SR-2

Q192874 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:
Q233537 OFF97: Year 2000 Related Problems Corrected in Office 97 SR-2


STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


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 information about determining if a year is a leap year, click the article number below to view the article in the Microsoft Knowledge Base:
Q118923 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:


    3. 
      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          : kb2000 xlformat xlformula 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: August 2, 1999