Solved

How to incorporate dates prior to 1900 in Excel formulas

Posted on 2013-05-29
3
407 Views
Last Modified: 2013-06-14
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
Comment
Question by:Steve_Brady
3 Comments
 
LVL 50

Expert Comment

by:teylyn
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

by:Steve
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

by:
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.

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

regards, barry
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now