Solved

# How to incorporate dates prior to 1900 in Excel formulas

Posted on 2013-05-29
407 Views
Hello,

Since Excel's date system does not include serial numbers for dates prior to 1900, is there a good way to specify these dates so that they can be used in calculations?

The only method I have found is to add some fudge factor such as 2000 to the year (e.g. 2013 would be 4013) whenever the year is required in a formula and then subtract the value of that fudge factor back out in the answer.

However, that can get a bit confusing so it would be nice if there is a way to do it without the fudge factor.

Thanks
0

LVL 50

Expert Comment

ID: 39206613
Hello Steve,

it's rare that date calculations need to be performed with dates before 1900. That's why there really is no good workaround.

You could split the dates up into separate cells for day, month and year and treat them in separate formulas for a rough estimate.

Can you provide a few more details about the use case, i.e. what calculations you need to perform with pre-1900 dates?

cheers, teylyn
0

LVL 24

Expert Comment

ID: 39206815
I agree with Teylyn, it depends upon the use and there is no general one solution which fits all.

There is the option to use the date in format "yyyymmdd" which would allow for any dates, but it is not really a "date" at this point and is just a large number. So then working with it depends on your needs.
example: 10660912 would represent 12th Sept 1066.
Using  =--TEXT(TODAY(),"YYYYMMDD")  will format todays date as a long number

You can add 2,000 as you say, which is probably the simplest way to maintain working with dates as dates.
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 39207509
Hello Steve,

Adding a multiple of 400 years has worked for me in some situations (400 because that's how often the leap year pattern repeats) but ex Excel MVP John Walkenbach has a free add-in called "Xdate" which gives you 8 new functions to handle pre-1900 dates - see here.

regards, barry
0

## Featured Post

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.