Excel: Entering Date Without Year Defaults to Incorrect Year

 Last reviewed: November 30, 1994
Article ID: Q90830
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.1 and 3.0
  • Microsoft Excel for the Macintosh, version 3.0
  • Microsoft Excel for OS/2, versions 2.2 and 2.21

Summary:

Entering a date into a cell in Microsoft Excel while omitting the year normally defaults to the current year. Dates, however, which are entered in certain formats may default to the wrong year in version 3.0 of Microsoft Excel for the Macintosh and Microsoft Excel for Windows.

More Information:

The following table shows how Excel displays and evaluates specific date entries in Excel version 3.0: 

Value Entered           Excels Displays         Value in Formula Bar


4/15                    15-Apr                  4/15/1992
4-15                    15-Apr                  4/15/1992
Apr 15                  Apr-15                  4/1/2015
April 15                Apr-15                  4/1/2015
Note that the last two entries default to the year 2015, instead of 1992. This is due to the default format which Excel applies to the cell (mmm-yy, rather than mmm-dd). The number entered is considered the year of the date, and the day defaults to the first of the month.

The workaround for this is to type dates using numeric values (4/15, for example, rather than Apr 15) and then apply the correct number format after entering a range of dates.

To apply a specific number format to a large area:

  1. Select the appropriate region(s) to format (use the COMMAND key on the Macintosh to select non-adjacent regions and the CTRL key in Windows).

  2. From the Format menu, choose Number.

  3. Select or enter an appropriate date format and choose the OK button.

This problem was corrected in Microsoft Excel version 4.0 and Microsoft Excel for OS/2 version 3.0.

Reference(s):

"Microsoft Excel User's Guide," version 3.0, for the Macintosh, pages 86-67 and 180-182

"Microsoft Excel User's Guide," version 3.0, for Windows, pages 93-96 and 188-192

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.1 2.10 2.20 2.21 3.0 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.