There are 3 tables, 1) Slots, 2) Overlapping Dates and their Owners, 3) Owners Factors
I need to calculate (preferrably using a spill formula, but not mandatory) for each Slot (table 1) the number of business days in Overlapping Dates for each overlapping Owner (table 2) multiplied by the correspondent Factor (table 3).
Lastly, I'll calculate the business days multiplied by the number of Owners for each slot and will reduce the above figure... but this is the "easy" part of this calculation.
Image and Data Tables available below.
Slots (table 1)
| Slot Start Date | Slot End Date | Slot Name |
|---|---|---|
| 21.Jun.2023 | 04.Jul.2023 | Slot 1 |
| 05.Jul.2023 | 18.Jul.2023 | Slot 2 |
| 19.Jul.2023 | 01.Aug.2023 | Slot 3 |
| 02.Aug.2023 | 15.Aug.2023 | Slot 4 |
| 16.Aug.2023 | 29.Aug.2023 | Slot 5 |
| 30.Aug.2023 | 12.Sep.2023 | Slot 6 |
| 13.Sep.2023 | 26.Sep.2023 | Slot 7 |
| 27.Sep.2023 | 10.Oct.2023 | Slot 8 |
| 11.Oct.2023 | 24.Oct.2023 | Slot 9 |
| 25.Oct.2023 | 07.Nov.2023 | Slot 10 |
| 08.Nov.2023 | 21.Nov.2023 | Slot 11 |
| 22.Nov.2023 | 05.Dec.2023 | Slot 12 |
| 06.Dec.2023 | 19.Dec.2023 | Slot 13 |
| 20.Dec.2023 | 02.Jan.2024 | Slot 14 |
Overlapping Dates and Owners (table 2)
| Overlap Start Date | Overlap End Date | Overlap Owners |
|---|---|---|
| 11.Sep.2023 | 30.Sep.2023 | Overlap Owner G |
| 25.Sep.2023 | 08.Oct.2023 | Overlap Owner E |
| 25.Sep.2023 | 06.Oct.2023 | Overlap Owner D |
| 27.Sep.2023 | 02.Oct.2023 | Overlap Owner J |
| 11.Oct.2023 | 24.Oct.2023 | Overlap Owner A |
| 25.Oct.2023 | 27.Oct.2023 | Overlap Owner A |
| 26.Oct.2023 | 11.Nov.2023 | Overlap Owner H |
| 30.Oct.2023 | 07.Nov.2023 | Overlap Owner J |
| 31.Oct.2023 | 01.Nov.2023 | Overlap Owner C |
| 31.Oct.2023 | 03.Nov.2023 | Overlap Owner A |
| 01.Nov.2023 | 01.Nov.2023 | Overlap Owner I |
| 01.Nov.2023 | 02.Nov.2023 | Overlap Owner B |
| 02.Nov.2023 | 03.Nov.2023 | Overlap Owner G |
| 08.Nov.2023 | 10.Nov.2023 | Overlap Owner B |
| 13.Nov.2023 | 17.Nov.2023 | Overlap Owner B |
| 13.Nov.2023 | 16.Nov.2023 | Overlap Owner F |
| 13.Nov.2023 | 13.Nov.2023 | Overlap Owner I |
| 15.Nov.2023 | 15.Nov.2023 | Overlap Owner K |
| 20.Nov.2023 | 24.Nov.2023 | Overlap Owner I |
| 20.Nov.2023 | 20.Nov.2023 | Overlap Owner A |
| 21.Nov.2023 | 21.Nov.2023 | Overlap Owner H |
| 23.Nov.2023 | 23.Nov.2023 | Overlap Owner G |
| 24.Nov.2023 | 24.Nov.2023 | Overlap Owner F |
| 30.Nov.2023 | 08.Dec.2023 | Overlap Owner I |
| 04.Dec.2023 | 08.Dec.2023 | Overlap Owner C |
| 06.Dec.2023 | 08.Dec.2023 | Overlap Owner B |
| 11.Dec.2023 | 15.Dec.2023 | Overlap Owner J |
| 13.Dec.2023 | 29.Dec.2023 | Overlap Owner F |
| 14.Dec.2023 | 22.Dec.2023 | Overlap Owner B |
| 16.Dec.2023 | 02.Jan.2024 | Overlap Owner K |
| 19.Dec.2023 | 19.Dec.2023 | Overlap Owner A |
| 19.Dec.2023 | 29.Dec.2023 | Overlap Owner A |
| 20.Dec.2023 | 01.Jan.2024 | Overlap Owner A |
| 20.Dec.2023 | 26.Dec.2023 | Overlap Owner I |
| 21.Dec.2023 | 29.Dec.2023 | Overlap Owner C |
| 27.Dec.2023 | 29.Dec.2023 | Overlap Owner E |
| 27.Dec.2023 | 29.Dec.2023 | Overlap Owner D |
| 27.Dec.2023 | 29.Dec.2023 | Overlap Owner L |
Owners Factors (table 3)
| Owners | Factor |
|---|---|
| Overlap Owner A | 0.5 |
| Overlap Owner B | 0.75 |
| Overlap Owner C | 0.375 |
| Overlap Owner D | 0.25 |
| Overlap Owner E | 0.5 |
| Overlap Owner F | 0.125 |
| Overlap Owner G | 0.25 |
| Overlap Owner H | 0.5 |
| Overlap Owner I | 0.75 |
| Overlap Owner J | 0.125 |
| Overlap Owner K | 0.375 |
| Overlap Owner L | 0.75 |
I tried SUMPRODUCT but I could not get the formula to calculate correctly the overlapping days as the formula below doesn't seem to work, let alone fit NETWORKINGDAYS in the middle of that. =SUMPRODUCT( (MAX( MIN( slot_end_date, overlapping_end_date ) - MAX( slot_start_date, overlapping_start_date+1 ), 0 ))
The idea of using MINIFS and MAXIFS came to my mind but apparently I'ld need to approach that via Lambda or even a number of Lambdas in a LET function. That was when I stopped worring that I would get stuck if excel limitations or with performance issues as the actual data is about 10 times bigger than the sample I share here.
So at the moment I have just put in what should be the total of business days for each slot so you can check them. As you say, multiplying by the factors should be fairly straightforward after that. The calculation is just a variation on the standard Min-Max formula.