Watch that Date!

Most of us these days have gotten into the habit of using four digits when referring to a year. It is convenient, of course, to use just two digits, but Beware, Excel uses some rules that may Surprise you when entering dates by using just two digits for the year. Two-digit year dates from 00 to 29 are interpreted as 21st century dates, but years from 30 to 99 are understood by Excel as 20th century dates.

For example, if you enter 7/4/29, Excel construes your entry as July 4, 2029. If, however, you enter 7/4/30, Excel interprets it to be July 4, 1930!

The reason this is so is because Excel uses a default boundary year of 2029. This can be changed, if you wish, by going to the Date, Time, Language, and Regional Options category on your Control Panel. Then click on Change the format of numbers, dates, and times and enter a different date on the Date tab after clicking the Customize button.

When working with data, Surprises can be Bad. The best practice is, obviously, to use four digits when entering the year. Of course, there is that Convenience thing…