Microsoft Office Tutorials and References
In Depth Information
Tip 39: Working with Pre-1900 Dates
Working with Pre-1900 Dates
According to Excel, the world began on January 1, 1900. If you work with historical information or do
genealogy research, you may have noticed that Excel doesn’t recognize pre-1900 dates. For example,
if you enter July 4, 1776, into a cell, Excel interprets it as text, not a date.
Unfortunately, the only way to work with pre-1900 dates is to enter the date into a cell as text. The
problem, however, is that you can’t perform any manipulation on dates recognized as text. For
example, you can’t change its numeric formatting, you can’t determine which day of the week this date
occurred on, and you can’t calculate the date that occurs seven days later.
Use three columns
To be able to sort by dates that precede 1900, enter the year, month, and day into separate cells.
Figure 39-1 shows a simple example.
Figure 39-1: To allow sorting by pre-1900 dates, enter the year, month, and day into separate cells.
To sort the presidents by birthday, first do an ascending sort on column D (day), an ascending sort
on column C (month), and finally, an ascending sort on column B (year). The result is shown in