Forum Discussion
Formula to check if date range falls within another date range
- 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","")))
Harun24HR Here is a link to the document https://docs.google.com/spreadsheets/d/1-sDskQ3eqWqT6xYlmohjm7KdsBJvupyg/edit?usp=sharing&ouid=113197352351852045522&rtpof=true&sd=true
I've only done a handful of expected results in this since the dataset is so large, but the orange highlighted rows should be showing 'Leave' in the CHECK# columns.
- 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","")