Solved

How to incorporate dates prior to 1900 in Excel formulas

Posted on 2013-05-29
3
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 50
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 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