How to incorporate dates prior to 1900 in Excel formulas

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
Steve_BradyAsked:
Who is Participating?
 
barry houdiniCommented:
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.

I've downloaded and used Xdate in the past with no problems

regards, barry
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
SteveCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.