Tips & Tricks‎ > ‎Excel Tips‎ > ‎

Inconsistent date entries

posted Jan 30, 2011, 7:25 AM by Ayush Jain

You need to exercise caution when entering dates by using two digits for the year. When you do so, Excel has some rules that kick in to determine which century to use. And those rules vary, depending on the version of Excel that you use. Two-digit years between 00 and 29 are interpreted as twenty-first century dates, and two-digit years between 30 and 99 are interpreted as twentieth century dates.

For example, if you enter 12/15/28, Excel interprets your entry as December 15, 2028. But if you enter 12/15/30, Excel sees it as December 15, 1930. This is because Windows uses a default boundary year of 2029.

You can keep the default as is, or change it by using the Windows Control Panel (use the spinner in the Calendar area of the Date tab of the Regional and Language Settings Properties dialog box).