Birth Date in Range

[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. ??

 

[Bob Phillips]

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

 

[Bob Phillips]

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)

 

[Chip Pearson]

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

 

[Rick Rothstein \(MVP - VB\)]

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. ??

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

 

[matt.albery]

Thank you all very much for your guidance!