Inconsistent date entries

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

Excel’s leap year bug

A leap year, which occurs every four years, contains an additional day (February 29). Although the year 1900 was not a leap year, Excel treats it as such. In other words, when you type 2/29/1900 into a cell, Excel does not complain. It interprets this as a valid date and assigns a serial number of 60. If you type 2/29/1901, however, Excel correctly interprets it as a mistake and doesn’t convert it to a date. Rather, it simply makes the cell entry a text string. How can a product used daily by millions of people contain such an obvious bug?

The answer is historical. The original version of Lotus 1-2-3 contained a bug that caused it to consider 1900 as a leap year. When Excel was released some time later, the designers knew of this bug and chose to reproduce it in Excel to maintain compatibility with Lotus worksheet files.

Why does this bug still exist in later versions of Excel? Microsoft asserts that the disadvantages of correcting this bug outweigh the advantages. If the bug were eliminated, it would mess up hundreds of thousands of existing workbooks. In addition, correcting this problem would affect compatibility between Excel and other programs that use dates. As it stands, this bug really causes very few problems

because most users do not use dates before March 1, 1900.