There are a number of relatively complicated macros and spreadsheet functions used to calculate dates prior to 1900 using Excel but I've come up with a rather simple, if dirty method, of accomplishing this little trick.

The need to do it is based on the fact that Excel doesn't understand dates prior to Jan 1, 1900.

The Concept

Basically there are two possible century equations, one with a leap day in the century year and one without. This is a vast oversimplification because of the changes between the Gregorian Calendar and the Julian Calendar but I said this method was a little dirty. What we're going to do is have a row for each century using Jan 1, 2000 to Dec 31, 2099 for centuries with the Leap Day and Jan 1, 2100 to Dec 31, 2199 for centuries without the Leap Day.

Create Column Headings in B1 of Start Date and C1 of End Date and D1 Total Days

Create Row headings for each century necessary; 18th in A2, 19th in A3, 20th in A4 etc.


Type Jan 1, 2000 as the Start Date and Dec 31, 2099 as the end date for each row in a century year that has a leap day (1400s and before, 1500s, 1600s, 2000s, 2400s, etc)

Likewise use Jan 1, 2100 and Dec 31, 2199 in centuries without a leap day (1700s, 1800s, 1900s, 2100s, 2200s, 2300s, 2500s, etc)

For the row in which you want the actual start date use that date (keeping the year, just changing the month and day) instead of Jan 1. For the row in which you want the actual end date use that date (again not changing the year) instead of Dec 31.

In the Total Days column create a formula of C2 - B2. This gives the total day difference. Because we substituted either the century range 2000 to 2099 or 2100 to 2199 we have an accurate range based on if there was a leap day or not.

Fill the formula down to the other columns and create a sum at the bottom. Tada!

The only hitch remaining is if your date starts prior to March 21, 1582. If this is the case then simply subtract 10 from your formula result. This accounts for the correction made in that year.

As I said, this is a bit dirty because not everyone accepted the Gregorian calendar at the same time and variations by nation exist. But, it's pretty good.

Tom Liberman's avatar

Tom is a technical trainer and self-published author as he tells everyone ... over and over and over. If you have an application question he's the guy!