Forum Discussion
Formula to check if date range falls within another date range
I am trying to determine if a date range falls within another date range for a specific ID.
Example:
Table1
| ID | Start Date | End Date |
| 1 | 10/9/2022 | 11/19/2022 |
| 2 | 2/12/2023 | 3/11/2023 |
Table2
| ID | Start Date | End Date | Value |
| 1 | 10/10/2022 | 11/10/2022 | Leave |
| 2 | 5/5/2024 | 5/21/2024 | Leave |
Expected Result
| ID | Result |
| 1 | Leave |
| 2 | (blank) |
- I was able to find a solution that provided what I needed.
=IF(COUNTIFS('Leave'!$A$2:$A$44850,"="&'Data'!$A2,'Leave!$B$2:$B$44850,"<="&'Data'!C2,'Leave'!$C$2:$C$44850,">="&'Data'!D2),"Leave","")
Harun24HR It seems to work for some but not working for all. I'm not able to attach any documents here yet, but I'll include some screenshots. The highlighted columns are where the formulas are and for the highlighted row/cells I would expect the value to be 'Leave' based on the data in the Leave tab.
Formula in my 'CHECK' columns:
=MAP(A2:A2106,LAMBDA(x,IF(COUNTIFS(Leave!A2:A44850,x,Leave!B2:B44850,">="&XLOOKUP(x,Data!A2:A2106,Data!C2:C2106),Leave!C2:C44850,"<="&XLOOKUP(x,Data!A2:A2106,Data!D2:D2106))>0,"Leave","")))
- Would you please share the sample file via google-drive or onedrive? Please show your desired output in sample file.
