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

• Microsoft Excel 97 for Windows

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

Although the date may appear as February 1, it actually has the correct serial value for January 1. Therefore, any formulas that use the date as part of a calculation return the correct value. 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

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)

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

For information about determining if a year is a leap year, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q118923
   TITLE     : XL: Method to Determine Whether a Year Is 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.

   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

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.