View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
JLatham JLatham is offline
external usenet poster
 
Posts: 3,365
Default what is the formula to change date of birth into age

Here is a 'simple' formula that will report a person's age effective the
current month, i.e. if their birthday is in current month, it will report
that next age even if the day of the month hasn't arrived yet. This 'current
month' condition applies to all formulas below. Assumes their DOB is in A1

=YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))

depending on how your age limit works, here's one that will show age only if
they have passed a specific point (in this formula, they must be 21 or older
for the age to show up, under 21 and "Underage" will show. This is all one
long formula, although it may break here
=IF(YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))20,YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1))),"Underage")

or if your limit is the other way around, must NOT have reached a certain
age to join up (must be under 65 to join - of course this lets toddlers join)

=IF(YEAR(NOW())-YEAR(A6)-((MONTH(NOW())<MONTH(A6)))<65,YEAR(NOW())-YEAR(A6)-((MONTH(NOW())<MONTH(A6))),"Too Old")

to filter out the toddlers also, this one allows you to set older than AND
younger than limits:
=IF(YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))20,IF(YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1)))<65,YEAR(NOW())-YEAR(A1)-((MONTH(NOW())<MONTH(A1))),"Too Old"),"Too Young")


"Eddie" wrote:

I am compiling a membership list and need the age because we have an age
limit . Is there a formula to do this using date of birth? Can this be done
without me having to work it out in my head each time!

I am using MSO Excel 2003

Really appreciate any advice.
Thanks.