Subtracting early dates to get a number of days

[norm282]

Hi,

I wish to subtract the date 11/18/1877 from 11/16/1968 to get a result showing the number of intervening days but excel does not want to do it.

When I take 11/18/1900 from 11/18/1968 and format the result to get a number with no decimals the result is 24835 as it should be; but it does not seem to work for dates earlier than 1/1/1900.

Please advise me what is happening and what I need to do to get the result in one go.

Your help is much appreciated.
Thank you

 

[goldfish]

Excel stores days as numbers. 1 is 1/1/1900, 0 is 1/0/1900, but anything less than that does not work. Excel only supports dates from 1/1/1900 (or 1/0/1900 if you count that) to 12/31/9999 (which is reperesented by the value 2,958,465).

Guess you are out of luck if you want to do it the simple way. You'll have to keep track of leap years yourself, keep in mind they happen every 4 years, but don't every 100 years except every 400 years they do happen. So 2000 was a leap year, while 1900, 1800, and 1700 weren't, but generally every year that is a multiple of 4 is.

~Gold Fish

 

[jim may]

try:

http://www.j-walk.com/ss/excel/files/xdate.htm

 

[Boller]

Assuming :

- Start dates column A
- End dates in column B
- Both columns formatted as dates in the format m/d/yyyy
- Dates in column A that are earlier than 1900 are entered and displayed as m/d/yyyy
- There are no end dates in column B earlier than 1900
- There are no start dates in column A earlier than the year 900

Then :-

=IF(ISERROR(B1-A1),EDATE(B1,12000)-(LEFT(A1,LEN(A1)-4)&RIGHT(A1,4)+1000),B1-A1)

 

[barry houdini]

=IF(ISERROR(B1-A1),EDATE(B1,12000)-(LEFT(A1,LEN(A1)-4)&RIGHT(A1,4)+1000),B1-A1)

Hello Boller,

Great stuff! I like it

 

[Boller]

Hello Boller,

Great stuff! I like it

Praise indeed from the master of date formulas!

 

[norm282]

Hi,
I am surprised to learn that excel does not have a simple way of calculating the ages of people who were born before 1900.
It must be a bit of a headache for any geniologists around.
Thank you all for your help.
Sincerely,
Norm

 

[norm282]

Hi Boller,

Following your directions, I entered 11/18/1877 in cell A1 and 11/16/1968 in cell B1. I formatted each of these cells as m/d/yyyy. I then copied your formula into cell C1 and the result I get is: #NAME?

I don't know enough to analyse your formula but would appreciate it if you could please tell me where I have erred!

Thank you for your help.
Norm

 

[goldfish]

You don't have the analysis toolpak enable, so EDATE() doesn't work. To fix this go to Tools -> Add ins... -> Analysis Toolpak -> Ok

Then the formula should work for you!

HTH,
~Gold Fish

 

[norm282]

Hi Goldfish,

Thank you for the help. It works nicely.
Norm