| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Test if a date falls within a given range of dates in Excel?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Test if a date falls within a given range of dates in Excel?
lindaledb wrote:
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Test if a date falls within a given range of dates in Excel?
"Glenn" wrote: lindaledb wrote: 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. Thanks Glen....that did exactly what I wanted! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Test if a date falls within a given range of dates in Excel?
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! "Glenn" wrote: lindaledb wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Test if a date falls within a given range of dates in Excel?
Hi,
Please find attached my workbook in which I have presented 3 alternatives. Hope these help. Anxiously awaiting your feedback. -- Regards, Ashsih Mathur www.ashishmathur.com "lindaledb" wrote in message ... 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! "Glenn" wrote: lindaledb wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Test if a date falls within a given range of dates in Excel?
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" wrote: Hi, Please find attached my workbook in which I have presented 3 alternatives. Hope these help. Anxiously awaiting your feedback. -- Regards, Ashsih Mathur www.ashishmathur.com "lindaledb" wrote in message ... 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! "Glenn" wrote: lindaledb wrote: 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Test if a date falls within a given range of dates in Excel?
Hi,
I can see the attachment in the reply. I you still cannot, please feel free to write in to me at . -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "lindaledb" wrote in message ... 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" wrote: Hi, Please find attached my workbook in which I have presented 3 alternatives. Hope these help. Anxiously awaiting your feedback. -- Regards, Ashsih Mathur www.ashishmathur.com "lindaledb" wrote in message ... 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! "Glenn" wrote: lindaledb wrote: 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Test if a date falls within a given range of dates in Excel?
lindaledb wrote:
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return TRUE if a date falls between two dates | Excel Worksheet Functions | |||
| Need true or false if a date falls between a date range | Excel Worksheet Functions | |||
| Return a specified date when it falls within a range.... | Excel Discussion (Misc queries) | |||
| How to find if a date falls between 2 dates | Excel Worksheet Functions | |||
| Can I check if a date falls between a range then sum other # if tr | Excel Worksheet Functions | |||
Hybrid Mode
