Calculate age

[MarkN]

Hello,

I am looking for a formula that will calculate how old a person is in years
and months, with months being rounded up to the nearest complete month. If
somebody was born yesterday, I would like the formula to return "0 years, 1
month".

I was 'experimenting' with:

=DATEDIF(E10,NOW(),"y") & " years, " & ROUNDUP(DATEDIF(E10,NOW(),"ym"),0) &
" months"

and while this is close, it's not right.

 

[WLMPilot]

Check this link out. It won't give you the rounded command, but it will get
you started.

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

Les

 

[vezerid]

Mark,
ROUNDUP would not work in this case anyway, since DATEDIF(,,"ym")
returns an integer. I am supplying a solution involving IF(), if I
understand your philosophy correctly.

=DATEDIF(E10,NOW(),"y") & " years, " &
DATEDIF(E10,NOW(),"ym")+IF(DATEDIF(E10,NOW(),"yd")>0,1,0) & " months"

Does this help?
Kostis Vezerides

 

[Roger Govier]

Hi Mark

Then use
=DATEDIF(E10,NOW(),"y") & " years, " &DATEDIF(E10,NOW(),"ym")+1 & "
months "

Obviously, as you are rounding up it will be overstating by a complete
month on 12 days of the year and will give the answer of 5 years 12
months for example, once someone passes the 11th month.

You could use some form of conditional test to the addition of the 1 if
required.

 

[MarkN]

Thanks for the solutions and explanations, both do exactly what I need
--
Thanks again,
MarkN

Hi Mark

Then use
=DATEDIF(E10,NOW(),"y") & " years, " &DATEDIF(E10,NOW(),"ym")+1 & "
months "

Obviously, as you are rounding up it will be overstating by a complete
month on 12 days of the year and will give the answer of 5 years 12
months for example, once someone passes the 11th month.

You could use some form of conditional test to the addition of the 1 if
required.

 

[rh33a]

Thank you so much!!!!
this link is really helpful.. Previously, i counted the age by =A2-A1/365,
but it didn't appear correctly as it rounded the 10-12 months as decimals.

I solved my problem with this link..

Thank you very much..

Cheers