convert date of birth to current age
 

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.

Try this:

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

A1 is where the Bdate is entered.

HTH,
Paul

"paralegal" wrote in message
...
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.

=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


"paralegal" wrote in message
...
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.

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))




"CLR" wrote in message
...
=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


"paralegal" wrote in message
...
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.

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

On Tue, 19 Apr 2005 13:40:05 -0700, paralegal
wrote:

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.

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")


On Tue, 19 Apr 2005 17:17:18 -0400, "PCLIVE" wrote:

Try this:

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

A1 is where the Bdate is entered.

HTH,
Paul

"paralegal" wrote in message
...
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.

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.

On Tue, 19 Apr 2005 17:41:41 -0400, "PCLIVE" wrote:

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))




"CLR" wrote in message
...

=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


"paralegal" wrote in message
...
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.

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



"Myrna Larson" wrote in message
...
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.

On Tue, 19 Apr 2005 17:41:41 -0400, "PCLIVE" wrote:

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))




"CLR" wrote in message
...

=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


"paralegal" wrote in message
...
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.

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.


On Wed, 20 Apr 2005 09:51:12 -0400, "PCLIVE" wrote:

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



"Myrna Larson" wrote in message
.. .
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.

On Tue, 19 Apr 2005 17:41:41 -0400, "PCLIVE" wrote:

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(NO W())),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1))




"CLR" wrote in message
...


=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


"paralegal" wrote in message
...
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.