I have a list of start dates & end dates. I need to assign a number to each row - the number should be the amount of other ranges (from the above rows only) that the particular row's range overlap with.
My plan is to use this value to assign events to rows on a calendar, so that there's no overlap. (Higher values will bump the event up by more rows)
| Start Date | End Date | Overlap Count |
|---|---|---|
| 21/09/2022 | 20/10/2022 | 0 |
| 18/10/2022 | 20/10/2022 | 1 |
| 20/10/2022 | 22/10/2022 | 2 |
| 23/12/2023 | 28/12/2023 | 0 |
| 19/10/2022 | 30/10/2022 | 3 |
In the above example I've populated column C with the expected result manually but I'm looking for a formula to find this.
I've managed to find other ways to check for overlaps and return TRUE or FALSE but can't find a way to count the amount of overlaps.
CodePudding user response：
C2:
=COUNTIF(LAMBDA(ca,cb,MAP(A$1:A1,B$1:B1,LAMBDA(a,b,OR(ISBETWEEN(SEQUENCE(b-a 1,1,a),ca,cb)))))(A2,B2),TRUE())
MAP each of the previous row values to a and b and check whether it's BETWEEN current a ca and current b cb
| Start Date | End Date | Overlap Count | COUNTIF(LAMBDA(ca,cb,MAP(A$1:A1,B$1:B1,LAMBDA(a,b,OR(ISBETWEEN(SEQUENCE(b-a 1,1,a),ca,cb)))))(A2,B2),TRUE()) |
|---|---|---|---|
| 2022-09-21 | 2022-10-20 | 0.00 | 0 |
| 2022-10-18 | 2022-10-20 | 1.00 | 1 |
| 2022-10-20 | 2022-10-22 | 2.00 | 2 |
| 2023-12-23 | 2023-12-28 | 0.00 | 0 |
| 2022-10-19 | 2022-10-30 | 3.00 | 3 |