years & months in excel

[keyboard flier]

Hi,
I am trying get an excel sheet to state an age in years and months but cannot do it.
I have a list of dates of birth and in the next column want to have the age in years and months in relation to todays date but all it will say is a the age to 1 decimal place, eg DOB 17/03/66 comes up with 43.8 years not 43 year 9 months.

Can somebody help, please.

[Cacophonix]

KeyboardFlier

This formula works for me. Will calculate the difference between today's date and your birthday in cell A2.

=DATEDIF(A2,TODAY(),"y")&" years "&DATEDIF(A2,TODAY(),"ym")&" months "&DATEDIF(A2,TODAY(),"md")&" days"

[MetoPower]

Enter date of birth in A1, the in A2 you may choose between:

=DATEDIF(A1,NOW(),"y")&"y,"&DATEDIF(A1,NOW(),"ym")&"m,"&DATE DIF(A1,NOW(),"md")&"d"

or

=DATEDIF(A1,TODAY(),"y")&" Years, "&DATEDIF(A1,TODAY(),"ym")&" Months and "&DATEDIF(A1,TODAY(),"md")&" Days"

You may replace "NOW" by "TODAY" or another date (or cell) and you may also remove the "days" bits.

MP

[DeeJayEss]

Handy functions.

An alternative could be to keep the 43.8 number and have another cell that says something like '=LEFT(cell ref, 2) & " Years, " & (RIGHT(cell ref, 2) * 12) & " Months."'

The only issue you have here is that you cannot format the 'RIGHT(cell ref, 2) * 12' part of the code to specify a single decimal point. You could do it if you put the right(cell, 2) value into another cell and changed its format that way, or used VBA, which would be very neat and fast.

So for 43.8, the formula gives "43 Years, 9.6 Months."

I should specify this is native - there are some options out there (check this site for example) of addons for XL that can give you text and string formatting options without having to delve into the exciting world of XL VBA.

HTH, or at least gives you something else to think about.

[keyboard flier]

Thank you all very much, works a treat now.