View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Glenn Glenn is offline
external usenet poster
 
Posts: 1,240
Default Calculate 'exact' age in EXCEL

Mike H wrote:
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(B1A1,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

On Jan 10, 6:35 am, Zanny Garbett wrote:
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