I have a data set that has client names, dates, and time in/out for each employee (xl2bb copied below). I need to be able to find and summarize the overlapping time where there were 2 or more staff serving a client.
I was able to calculate the number of total hours per line in column H. What I need in a separate column, is just the overlapping time in hours. (Column i) I hard coded the values that I'm looking to get (i3, i9) when there are two entries that overlap. I am even more lost when there are multiple lines that overlap (shaded blue rows 10, 11, 13, 14)
Alternatively, if there is a better way to summarize, by date, by client, the number of hours that are done with 1 staff, 2 staff, 3 staff? Something like the second xl2bb example below?
Thank you for any help you can offer!
DATASET
Summarized Results
I was able to calculate the number of total hours per line in column H. What I need in a separate column, is just the overlapping time in hours. (Column i) I hard coded the values that I'm looking to get (i3, i9) when there are two entries that overlap. I am even more lost when there are multiple lines that overlap (shaded blue rows 10, 11, 13, 14)
Alternatively, if there is a better way to summarize, by date, by client, the number of hours that are done with 1 staff, 2 staff, 3 staff? Something like the second xl2bb example below?
Thank you for any help you can offer!
DATASET
book1.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Client | Date | Staff | In | Out | Location | TOTAL HOURS | OVERLAP HOURS | |||
2 | A | 9/16/2022 | X | 2:30 PM | 11:00 PM | Townsend | 8.50 | ||||
3 | A | 9/16/2022 | Y | 10:00 PM | 12:00 AM | Townsend | 2.00 | 1.000 | |||
4 | A | 9/16/2022 | X | 1:00 PM | 2:30 PM | Townsend | 1.50 | ||||
5 | B | 9/16/2022 | X | 12:00 AM | 6:00 AM | Townsend | 6.00 | ||||
6 | A | 9/16/2022 | Y | 6:00 AM | 8:03 AM | Townsend | 2.05 | ||||
7 | A | 9/17/2022 | Y | 12:00 AM | 7:00 AM | Townsend | 7.00 | ||||
8 | B | 9/17/2022 | X | 7:00 AM | 8:08 AM | Townsend | 1.13 | ||||
9 | B | 9/17/2022 | Y | 7:51 AM | 7:58 PM | Townsend | 12.12 | 0.283 | |||
10 | C | 9/18/2022 | Z | 12:00 AM | 9:18 AM | Townsend | 9.30 | ||||
11 | C | 9/18/2022 | Y | 8:16 AM | 6:40 PM | Townsend | 10.40 | ||||
12 | C | 9/18/2022 | X | 11:18 PM | 12:00 AM | Townsend | 0.70 | ||||
13 | C | 9/18/2022 | W | 8:40 PM | 11:11 PM | Townsend | 2.52 | ||||
14 | C | 9/18/2022 | V | 4:22 PM | 11:14 PM | Townsend | 6.87 | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H2:H14 | H2 | =MOD(E2-D2,1)*24 |
Summarized Results
book1.xlsx | ||||||
---|---|---|---|---|---|---|
L | M | N | O | |||
1 | Client | Date | 1:1 Hours | 2:1 Hours | ||
2 | A | 9/16/2022 | 20.05 | 1 | ||
3 | B | 9/16/2022 | 6 | |||
4 | A | 9/17/2022 | 7 | |||
5 | B | 9/17/2022 | 12.967 | 0.283 | ||
Sheet1 |