Subtracting early dates to get a number of days

G

goldfish

Well-known Member

Joined

Aug 23, 2005

Messages

712

• Sep 19, 2007

• #2

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

 

Upvote 0

jim may

Well-known Member

Joined

Jul 4, 2004

Messages

7,486

• Sep 19, 2007

• #3

try:

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

 

Upvote 0

B

Boller

Banned

Joined

Apr 11, 2006

Messages

2,328

• Sep 19, 2007

• #4

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)

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Sep 19, 2007

• #5

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

Click to expand...

Hello Boller,

Great stuff! I like it

 

Upvote 0

B

Boller

Banned

Joined

Apr 11, 2006

Messages

2,328

• Sep 20, 2007

• #6

Hello Boller,

Great stuff! I like it

Click to expand...

Praise indeed from the master of date formulas!

 

Upvote 0

N

norm282

New Member

Joined

Nov 11, 2002

Messages

22

• Sep 20, 2007

• #7

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

 

Upvote 0

N

norm282

New Member

Joined

Nov 11, 2002

Messages

22

• Sep 20, 2007

• #8

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

 

Upvote 0

G

goldfish

Well-known Member

Joined

Aug 23, 2005

Messages

712

• Sep 20, 2007

• #9

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

 

Upvote 0

N

norm282

New Member

Joined

Nov 11, 2002

Messages

22

• Sep 20, 2007

• #10

Hi Goldfish,

Thank you for the help. It works nicely.
Norm

 

Upvote 0