Hello,
I am trying to find a way to determine how much overlapping time each person in Proj1 has with people in Proj2. I have a table entry in Proj1 that lets me know how many overlaps in time there are, and I can individually tally up the amount of overlap for each person using the Median functionality, but I cant seem to figure out how to make all that happen in one neat and tidy formula. I would like the formula in F3:F12 (Proj1[Overlap time]) to run that median formula for each of the entries in Proj2, and sum them. It should give me the results that I have shown in J3:J9. I am not sure why blank cells are returning strange results highlighted in orange... and one more complicating factor is that it is possible for these times to span across two days, im not sure how this will react if a shift runs past midnight.
Please help, any help is greatly appreciated.
I am trying to find a way to determine how much overlapping time each person in Proj1 has with people in Proj2. I have a table entry in Proj1 that lets me know how many overlaps in time there are, and I can individually tally up the amount of overlap for each person using the Median functionality, but I cant seem to figure out how to make all that happen in one neat and tidy formula. I would like the formula in F3:F12 (Proj1[Overlap time]) to run that median formula for each of the entries in Proj2, and sum them. It should give me the results that I have shown in J3:J9. I am not sure why blank cells are returning strange results highlighted in orange... and one more complicating factor is that it is possible for these times to span across two days, im not sure how this will react if a shift runs past midnight.
Please help, any help is greatly appreciated.
| Book3.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Proj1 | overlap w/ Fred | overlap w/ George | total | ||||||||
| 2 | Start | End | Name | time | #Overlaps | Overlap time | ||||||
| 3 | 6:00 | 7:20 | Billy | 1:20 | 0 | 0:44 | 0:00 | 0:00 | 0:00 | |||
| 4 | 7:20 | 8:35 | Bob | 1:15 | 1 | 0:44 | 0:21 | 0:00 | 0:21 | |||
| 5 | 8:35 | 9:48 | Sally | 1:13 | 2 | 0:36 | 1:08 | 0:05 | 1:13 | |||
| 6 | 9:48 | 10:15 | Rachel | 0:27 | 1 | 0:08 | 0:00 | 0:12 | 0:12 | |||
| 7 | 10:15 | 11:37 | Joe | 1:22 | 0 | 0:08 | 0:00 | 0:00 | 0:00 | |||
| 8 | 11:37 | 12:49 | Mary | 1:12 | 0 | 0:08 | 0:00 | 0:00 | 0:00 | |||
| 9 | 12:49 | 13:00 | Ralf | 0:11 | 0 | 0:08 | 0:00 | 0:00 | 0:00 | |||
| 10 | 13:00 | CLSD | 0 | 0:17 | 0:44 | 0:08 | 0:53 | |||||
| 11 | 0:53 | 1:29 | 0:17 | 1:46 | ||||||||
| 12 | 0:53 | 1:29 | 0:17 | 1:46 | ||||||||
| 13 | ||||||||||||
| 14 | ||||||||||||
| 15 | Proj2 | |||||||||||
| 16 | Start | End | Name | time | ||||||||
| 17 | 8:14 | 9:43 | Fred | 1:29 | ||||||||
| 18 | 9:43 | 10:00 | George | 0:17 | ||||||||
| 19 | ||||||||||||
| 20 | ||||||||||||
| 21 | ||||||||||||
| 22 | ||||||||||||
| 23 | ||||||||||||
| 24 | ||||||||||||
| 25 | ||||||||||||
| 26 | ||||||||||||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3:D12,D17:D26 | D3 | =IF([@End]="","",[@End]-[@Start]) |
| E3:E12 | E3 | =IF([@Start]="","",SUMPRODUCT(([@Start]<=Proj2[End])*([@End]>=Proj2[Start]))) |
| F3:F12 | F3 | =SUM(MEDIAN([@Start],[@End],Proj2[End])-MEDIAN([@Start],[@End],Proj2[Start])) |
| H3:H12 | H3 | =MEDIAN(Proj1[@Start],Proj1[@End],$B$17)-MEDIAN(Proj1[@Start],Proj1[@End],$A$17) |
| I3:I12 | I3 | =MEDIAN(Proj1[@Start],Proj1[@End],$B$18)-MEDIAN(Proj1[@Start],Proj1[@End],$A$18) |
| J3:J12 | J3 | =SUM(H3:I3) |
