Calculate 'exact' age in EXCEL

[Zanny Garbett]

Try this with:

[A1] = date of birth;
[B1] = Date of death;

=DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"&DATEDIF(A1,B1,"md")&"d"

Should work OK

Regards
Zanny

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

 

[Mike H]

Hi,

I've never understood the value of months in calculating a time
period. Try these 2 dates with your formula and you will get

7y,1m,-2d

a1= 31/01/2002
b1= 01/03/2009

It returns this type of result if the first month is longer than the
second.

Mike

 

[Glenn]

Hi,

I've never understood the value of months in calculating a time
period.

I agree. This should be sufficient:

=DATEDIF(A1,B1,"y")&"y, "&DATEDIF($A$1,$B$1,"yd")&"d"

If you insist on months, try this:

=IF(B1>A1,DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "&
IF(DAY(A1)>DAY(B1-DAY(B1)),DAY(B1),DAY(B1)+DAY(B1-DAY(B1))-DAY(A1))&"d",NA())

although you will get the same answer for the difference between January
28th-31st and any date after February.

Try these 2 dates with your formula and you will get

7y,1m,-2d

a1= 31/01/2002
b1= 01/03/2009

It returns this type of result if the first month is longer than the
second.

Not exactly, considering both January and March have 31 days...it is February
that is throwing off the calculation. The difference between the 31st day of
February in 2009 (evaluates as March 3rd) and the first day of March in 2009 is -2.

Mike

Try this with:

[A1] = date of birth;
[B1] = Date of death;

=DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"&DATEDIF(A1,B1,"md")&"d"

Should work OK

Regards
Zanny

EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com/default.aspx?ref=ng

 

[Glenn]

If you insist on months, try this:

=IF(B1>A1,DATEDIF(A1,B1,"y")&"y, "&DATEDIF(A1,B1,"ym")&"m, "&
IF(DAY(A1)>DAY(B1-DAY(B1)),DAY(B1),DAY(B1)+DAY(B1-DAY(B1))-DAY(A1))&"d",NA())

Actually, there is something wrong with this...not sure what and no time to
figure it out right now.

 

[Niek Otten]

What is your question?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

in message news:[email protected]...

 

[mkmoss]

Pre 1900

Formula was very helpful...

> [A1] = date of birth;
> [B1] = Date of death;
>
> =DATEDIF(A1,B1,"y")&"y,"&DATEDIF(A1,B1,"ym")&"m,"&DATEDIF(A1,B1,"md")&"d"

how do you calculate dates pre-1900? IE.,
> [A1] = 01/01/1897;
> [B1] = 01/01/1997

Thanks,
mk