Working With Pre-1900 Dates

Category: Formulas | [Item URL]

A companion file is available: Click here to download

In the eyes of Excel, the world began on January 1, 1900. Excel is not capable of working with dates earlier than that.

People who use Excel to store historical information often need to work with pre-1900 dates. The only way to create a date such as July 4, 1776, in Excel is to enter it into a cell and have the program interpret it as text. Unfortunately, you can't manipulate dates stored as text -- if you want to alter their formatting, for example, or if you need to calculate the day of the week they fell on.

To address this problem, I created an add-in (for Excel 97 or later versions) called Extended Date Functions. With this add-in installed, you'll have access to eight new worksheet functions that let you work with dates in any year from 0100 through 9999.

Note: Be careful if you plan to insert dates that occurred before 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.