Birth Date in Range

  • Thread starter Thread starter matt.albery
  • Start date Start date
M

matt.albery

I have a column with birth dates. I want to assign the individual
birth dates to age groups (21-30, 31-40, etc.). How can I get Excel
to calculate if a birth date falls into a date range, and have it
assign a value.

Is there a function that I could do that is like =IF(C2 IS BETWEEN
6/20/1927 AND 6/20/1937,"X") where C2 is the individual's birth date,
and "X" is placed in the column to symbolize that the individual falls
into that age group. ??
 
=IF(DATEDIF(birth_date,TODAY(),"y")<21,"",IF(DATEDIF(birth_date,TODAY(),"y")<31,"21-30",IF(DATEDIF(birth_date,TODAY(),"y")<41,"31-40",etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Better

=LOOKUP(DATEDIF(A29,TODAY(),"y"),{0,21,31,41,51,61},{"Under
21","21-30","31-40","41-50","51-60","60+"})

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Matt,

Use the AND function with the DATE function to test if C2 is within the
desired interval. Change the "<=" and ">=" to "<" and ">" depending on how
you want to treat dates that fall on June 20th of the year.

=IF(AND(C2>=DATE(1927,6,20),C2<=DATE(1937,6,20)),"X","Not X")
 
I have a column with birth dates. I want to assign the individual
birth dates to age groups (21-30, 31-40, etc.). How can I get Excel
to calculate if a birth date falls into a date range, and have it
assign a value.

Is there a function that I could do that is like =IF(C2 IS BETWEEN
6/20/1927 AND 6/20/1937,"X") where C2 is the individual's birth date,
and "X" is placed in the column to symbolize that the individual falls
into that age group. ??
Click to expand...

It is not clear what you want displayed for your ranges. Assuming the 10
year span was what you were looking for, something like this should work...

=CHOOSE(1+INT(DATEDIF(A1,TODAY(),"y")/10),"Under 21","Under 21","21 -
30","31 - 40","41 - 50","51 - 60","61 - 70","71 - 80","81 -
90","91 -100","100 - 110", "111-120")

Rick
 
Back
Top