How can I check if an input_date falls between two dates (a date range) in excel, where the date range exists in a list of date ranges?
So I need to check if input_date falls between any date range, and then if it does, return the value of the date ranged it matched with. See the below table for an example.
| Month | Start Date | End Date |
|---|---|---|
| Month 1 | 1/1/2022 | 1/31/2022 |
| Month 2 | 2/1/2022 | 2/27/2022 |
| Month 3 | 3/1/2022 | 3/31/2022 |
Input vs Expected Result
| input_date | Expected Result = Month |
|---|---|
| 1/25/2022 | Month 1 |
| 2/3/2022 | Month 2 |
I've tried using =IF(AND(A2>StartDate,A2<EndDate),TRUE, FALSE) but how can I check A2 against all date ranges in a list, and output the corresponding Month value? Is the best way really just nesting if statements for a fixed number of ranges? Any dynamic approach?
CodePudding user response：
You may try using the either of formulas like shown below,
• Formula used in cell B7
=XLOOKUP(1,(A7>=$B$2:$B$4)*(A7<=$C$2:$C$4),$A$2:$A$4,"")
If one do not have access to XLOOKUP() then INDEX() & MATCH() can also be used,
• Formula used in cell C7
=INDEX($A$2:$A$4,MATCH(1,(A7>=$B$2:$B$4)*(A7<=$C$2:$C$4),0))
CodePudding user response：
If you just require the text output of the month the input date is referring to then can you not just:
=TEXT(A2,"mmmm")
Or do you have a need for something outside of your example?
CodePudding user response：
You can use the FILTER function to return the appropriate item from the "Month" column
If your Date Ranges are in a table named dateRange & the Input_Dateis inG2`:
=FILTER(dateRange[Month],(G2>=dateRange[Start Date]) * (G2<=dateRange[End Date]))
