How to enter historical dates in Excel

When working with historical dates in Excel, you need to be aware of two things:

The first is that when you enter a 2-digit year, Excel will assign a century to create a valid date using a special rule. When the year is 29 or less, Excel will use the twenty-first century. When the year is 30 or greater, Excel will use the twentieth century.

The second thing to be aware of is that Excel does not recognize any dates before Jan 1, 1900.

Let's take a look.

Here we have a list of birthdays. Our first three birthdays have a year that's greater than 29. When we enter the year using two digits only, Excel will use the twentieth century, and assign the date to the 1900's.

The next three birthdays all have a year that's less than 30. If we try to use a 2-digit year, Excel will assign the date to the twenty-first century, which is incorrect.

In this case, we need to use a 4-digit year.

The last birthday is before January 1, 1900.

Excel doesn't recognize this value as a date at all. Instead, Excel treats the value as text and aligns the contents of the cell to the left.

If you're working with dates earlier than 1900, you'll need to handle the formatting yourself and you won't be able to use these dates in formulas.