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.


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy