Forum Discussion

TCatron18's avatar
TCatron18
Copper Contributor
Sep 26, 2024

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 ...
Excel
Formulas and Functions
  • TCatron18's avatar
    TCatron18
    Sep 30, 2024
    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's avatar
Harun24HR
Bronze Contributor
Sep 26, 2024

TCatron18 Try the following formula. Download the attached file.

 

=MAP(A8:A9,LAMBDA(x,IF(COUNTIFS(F2:F10,x,G2:G10,">="&XLOOKUP(x,A2:A5,B2:B5),H2:H10,"<="&XLOOKUP(x,A2:A5,C2:C5))>0,"Leave","")))

 

 

 

 

  • TCatron18's avatar
    TCatron18
    Copper Contributor
    Sep 26, 2024

    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","")))

     

     

Resources