Search code examples
excelexcel-formula

List all values of overlapping dates

I have a list of projects that have overlapping dates; see example screenshot below.

enter image description here

In cell P1, I have the below formula which is giving me a count of overlapping projects. I have inserted some filters to only focus on specific subsets as well.

=SUMPRODUCT((F2<=FILTER($G$2:$G$8887,$N$2:$N$8887=N2,$B$2:$B$8887=B2))*(G2>=FILTER($F$2:$F$8887,$N$2:$N$8887=N2,$B$2:$B$8887=B2))))

For row 2, I can manually find the 10 overlapping projects but is there a formula that can return these 10 projects? I have quite a long list and doing it manually is not really an option.

Solution

  • It can be a solution for you:

    [D2:D31]=TEXTJOIN(";",,
  FILTER($A$2:$A$31,
    BYROW($B$2:$C$31,
      LAMBDA(a,
        OR(
          AND(B2>=INDEX(a,1,1),B2<=INDEX(a,1,2)),
          AND(C2>=INDEX(a,1,1),C2<=INDEX(a,1,2))
        )
      )
    )
  )
)

    enter image description here

    Two projects are considered overlapped if the start date or the end date of the first project are within dates of the second project.

    The advanced formula provides the clearer result:

    =IFERROR(TEXTJOIN(";",,FILTER($A$2:$A$31,BYROW($A$2:$C$31,LAMBDA(a,AND(A2<>INDEX(a,1,1),OR(AND(B2>=INDEX(a,1,2),B2<=INDEX(a,1,3)),AND(C2>=INDEX(a,1,2),C2<=INDEX(a,1,3)))))))),"")

    enter image description here

    The improved formula:

    =TEXTJOIN(";",TRUE,BYROW($A$2:$C$31,LAMBDA(a,IF(AND(A2<>INDEX(a,1,1),MIN(C2,INDEX(a,1,3))>=MAX(B2,INDEX(a,1,2))),INDEX(a,1,1),""))))