convert date of birth to current age

P

paralegal

is there an easy way to do this through a formula? I tried using the year
function and subtracting from current date but is doesn't caluculate
correctly if the birthday hasn't yet occured in current year. Thank you.
 
P

PCLIVE

Try this:

=ROUNDDOWN((TODAY()-A1)/365,0)

A1 is where the Bdate is entered.

HTH,
Paul
 
C

CLR

=IF(AND(MONTH(A1)>=MONTH(NOW()),DAY(A1)>=DAY(NOW())),YEAR(NOW())-YEAR(A1),YE
AR(NOW())-YEAR(A1)-1)

All on one line, watch out for email wordwrap..........

Vaya con Dios,
Chuck, CABGx3
 
P

PCLIVE

Though I didn't really see the need to use such a complicated formula, your
formula was slightly off. The "-1" at the end appears to be in the wrong
place. I've adjusted it below.

=IF(AND(MONTH(A1)>=MONTH(NOW()),DAY(A1)>=DAY(NOW())),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1))
 
G

Gord Dibben

Assume birthdate in A6 and today date in A7

=DATEDIF(A6,A7,"y") & "years," & DATEDIF(A6,A7,"ym") & "months,"&
DATEDIF(A6,A7,"md") & "days,"

Watch for word wrap. Formula is one line.


Gord Dibben Excel MVP
 
M

Myrna Larson

Try your formula with a birthdate of 4/29/1920 and the current date of
4/19/2005. I get 85.03, which rounds down to 85. The person won't be 85 for
another 10 days.

Then try =DATEDIF(A1,TODAY(),"y")
 
M

Myrna Larson

See my response to your first post for reasons why a more complicated formula
is needed. The AVERAGE number of days per year is 365.25, not 365, but you
can't modify your formula to substitute 365.25 for 365, as it still gives
errors when the current date is very close to the anniversary of the
birthdate.
 
P

PCLIVE

You are correct. Though I found and corrected the mistake in the formula
that was on the right track, I forgot to take into account that every year
does not have 365 days.

Thank you for pointing that out.
Regards,
Paul
 
M

Myrna Larson

Note that there is NO WAY to divide elapsed days by number of days in a year
and get a result that will increment the year EXACTLY on the anniversary date
(i.e. not a day early or a day late). One needs specialized functions that
deal with dates and the vagaries of the calendar to accomplish this.
 
Top