Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to incorporate dates prior to 1900 in Excel formulas

Posted on 2013-05-29
3
Medium Priority
?
455 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 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

721 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