Excel: Entering Date Without Year Defaults to Incorrect Year

ID: Q90830

The information in this article applies to:

Microsoft Excel for Windows, versions 2.1, 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:

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).

From the Format menu, choose Number.

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.

REFERENCES

For more information, please see the following article in the Microsoft Knowledge Base:

Q164406 : XL: How Microsoft Excel Works with Two-Digit Year Numbers

"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

Additional query words: 2.1 2.20 2.21 3.0

Keywords :
Version :
Platform :
Issue type :