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(DATED IF(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)



wrote in message
oups.com...
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. ??

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)



wrote in message
oups.com...
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. ??

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





wrote in message
oups.com...
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. ??

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

Thank you all very much for your guidance!