Display a person's age in years & months: for example 4;11

[Guest]

How do you show a person's chronological age in years & months in Excel: I
want the age to show up as 4;11 (4 yr 11 mo), not as 4.91. Thanks!

 

[dcronje]

long winded but it works for me.

=ROUNDDOWN(((TODAY()-A1)/365.25),0
&";"&ROUNDDOWN(MOD((TODAY()-A1),365.25)/365.25*12,0)


A1 contains the persons birth date

 

[mzehr]

Hi,
Try
=DATEDIF(A1,NOW(),"y") & ";" & DATEDIF(A1,NOW(),"ym") See
Chip Pearson's website for more info:


http://www.cpearson.com/excel/datedif.htm

HTH

 

[Myrna Larson]

As with all formulas that approximate the year length as 365.25 days, this
formula doesn't work when the 2nd date is close to or on the birthday.

I modified your formula to substitute a reference to cell A2 for TODAY(). Then
with the dates 2/27/2003 in A1 and 2/27/2004 in A2, your formula gives 0;11
instead of 1;00.


=ROUNDDOWN(((A2-A1)/365.25),0)&";"&ROUNDDOWN(MOD((A2-A1),365.25)/365.25*12,0)

With 2/27 as the month and day, the formula fails every 4 years, giving 4;11
instead of 5;0, 8;11 instead of 9;0, 12;11 instead of 13;0, etc.

If the person was born in a leap year, but after 2/29, and the 2nd date is the
birthday, the formula result is correct in leap years, i.e. ONLY 1 year out of
4.

To get the same answers as most people would derive based on the calendar, you
have to a formula like DATEDIF. See the other response in this thread.