Test if a date falls within a given range of dates in Excel?

[lindaledb]

I am trying to test if an individual's birthdate falls within any of the
given age division date ranges. I would like for it to output the age
division name. Below is a chart showing the current fields on my
spreadsheet. Any suggestions are greatly appreciated.


Beginning 4-H Year 2008
Member's Date of Birth 9/2/2008

Too Young DOB Range 9/1/2008 8/31/2002
Clover Kid DOB Range 9/1/2002 8/31/2000
Junior DOB Range 9/1/2000 8/31/1997
Intermediate DOB Range 9/1/1997 8/31/1994
Senior DOB Range 9/1/1994 8/31/1989
Too Old DOB Range 9/1/1989 8/31/1908

 

[Glenn]

I am trying to test if an individual's birthdate falls within any of the
given age division date ranges. I would like for it to output the age
division name. Below is a chart showing the current fields on my
spreadsheet. Any suggestions are greatly appreciated.


Beginning 4-H Year 2008
Member's Date of Birth 9/2/2008

Too Young DOB Range 9/1/2008 8/31/2002
Clover Kid DOB Range 9/1/2002 8/31/2000
Junior DOB Range 9/1/2000 8/31/1997
Intermediate DOB Range 9/1/1997 8/31/1994
Senior DOB Range 9/1/1994 8/31/1989
Too Old DOB Range 9/1/1989 8/31/1908

Assuming your data above is in A1:C9, put the following in C2:

=INDEX(A4:A9,MATCH(B2,B4:B9,-1))

Also, you should probably put =TODAY() in B4.

 

[lindaledb]

Assuming your data above is in A1:C9, put the following in C2:

=INDEX(A4:A9,MATCH(B2,B4:B9,-1))

Also, you should probably put =TODAY() in B4.

Thanks Glen....that did exactly what I wanted!

 

[lindaledb]

On second thought.....

After testing this further and looking at the code you suggested, I see that
in the formula you provided =INDEX(A4:A9,MATCH(B2,B4:B9,-1)) that it is only
looking up the dates in column B. I tried expanding that to B4:C9, but that
messes everything up.

Each age division has its own date range. For example, a Junior must have
been born between 9/1/2000 and 8/31/1997 (as seen in cells B6 and C6). I'm
not all that familiar with Index and Match, so when you suggested the range
of B4:B9, I'm assuming that Excel is looking from 9/1/2008 to 9/1/1989
instead of each individual row's range???? Am I making sense?

Is there an easier way to organize this or to alter the formula?

Again, thanks for your (or other's) suggestions!

 

[lindaledb]

I don't see an attachment. Am I missing something? Thanks for providing
some solutions. I look forward to reviewing them.

Sincerely,
Derrick

 

[Ashish Mathur]

Hi,

I can see the attachment in the reply. I you still cannot, please feel free
to write in to me at (e-mail address removed).

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Glenn]

On second thought.....

After testing this further and looking at the code you suggested, I see that
in the formula you provided =INDEX(A4:A9,MATCH(B2,B4:B9,-1)) that it is only
looking up the dates in column B. I tried expanding that to B4:C9, but that
messes everything up.

Each age division has its own date range. For example, a Junior must have
been born between 9/1/2000 and 8/31/1997 (as seen in cells B6 and C6). I'm
not all that familiar with Index and Match, so when you suggested the range
of B4:B9, I'm assuming that Excel is looking from 9/1/2008 to 9/1/1989
instead of each individual row's range???? Am I making sense?

Is there an easier way to organize this or to alter the formula?

Again, thanks for your (or other's) suggestions!

Sorry I didn't get back to you sooner.

From what I can tell, your ranges don't overlap and there are no gaps between
ranges, making the end date irrelevant. The formula only needed to find the
smallest start date that is greater than or equal to the Date of Birth.

Despite your worries, when you did further testing did you get the correct result?

Although it may not be much help, try looking at the help file for INDEX and MATCH.