Forum Discussion
Calculate overlapping hours
Given that example, it's incredibly easy to calculate the 3.5 hours. Simply subtract the minimum of Lucy's start times from the maximum of her end times.
(making assumptions about which columns contain the relevant data),
time spent supervising =MAX(D2:D3)-MIN(C2:C3)
Granted, that doesn't deal with other staff members and other days; just wanted to make the point that a simple example doesn't require much complications in solving.
But as you point out in asking the question, it's a very simplified example. Give us a more typical full day, complete with some examples that make it complicated. Along the way, can you explain the rules here that govern Lucy's (and other's) staff compensation, in particular
- Are they only paid for time supervising?
- Are they paid more for time supervising than time spent at work but not supervising?
- If they're paid a straight hourly (or salary) regardless of whether time is spent supervising, do you still need to know specifically the amount of time spent supervising? Why? How is it used?
By the way, you should expect it to be possible, whatever it is. Excel is very powerful; the challenge often is getting the problem clearly set forth and articulated.
Yes, the real-world situation is 300+ volunteers entering shifts across 3 summer months at 28 locations with 30-40 supervisors, and shifts aren't necessarily adjacent, so Lucy might supervise 2 volunteers from 10:00-noon, 1 volunteer from 11:30am-1pm, and then have an hour off and then have volunteers in from 3:00-5:00pm, so just taking MIN to MAX (10am-5pm) wouldn't be accurate. Some volunteers come in every day, some only once a week, but by the end of the summer, I have upwards of 10,000 rows of time entries.
I have had to just report volunteer hours, but now I need to report how many staff hours were spent supervising volunteers, so I would change how time is logged to have start and end time, instead of total hours.
This is one month of time entries at one location. Not difficult to go through and manually calculate, but time consuming at scale.
Volunteer Name Date Start Time End Time Supervisor
G 5/17/2025 10:00 AM 1:15 PM 2
L 5/17/2025 10:00 AM 12:00 PM 1
O 6/6/2025 1:00 PM 2:00 PM 2
O 6/9/2025 2:00 PM 4:00 PM 2
W 6/9/2025 3:00 PM 5:00 PM 2
T 6/10/2025 10:00 AM 12:00 PM 2
I 6/11/2025 10:00 AM 12:00 PM 3
S 6/11/2025 1:00 PM 3:00 PM 1
T 6/11/2025 2:00 PM 4:00 PM 3
T 6/12/2025 11:00 AM 1:00 PM 2
Z 6/12/2025 11:00 AM 5:00 PM 3
B 6/13/2025 2:00 PM 4:00 PM 2
D 6/13/2025 1:00 PM 3:00 PM 2
N 6/13/2025 2:00 PM 4:00 PM 2
(wouldn't publish copied table so sorry for lack of formatting)
Hi volunteersfpl,
Does the following formula meet your requirements? The formula works in Excel 365.
=LET( table, A1:D15, d, DROP(table, 1), date, CHOOSECOLS(d, 2), startt, CHOOSECOLS(d, 3), endt, CHOOSECOLS(d, 4), dr, DROP(GROUPBY(date, endt, MAX,, 0) * 24,, 1), VSTACK(HSTACK(TAKE(TAKE(table, 1),, 2), "Hours"), CHOOSECOLS(HSTACK(GROUPBY(date, CHOOSECOLS(d, 1), ARRAYTOTEXT,, 0), (BYROW(IFNA(--TEXTSPLIT(TEXTJOIN(";",, BYROW(--TEXTSPLIT(TEXTJOIN(";",, BYROW(IFNA(HSTACK(--TEXTSPLIT(TEXTJOIN(";",, BYROW(IFNA(--TEXTSPLIT(TEXTJOIN(";",, DROP(GROUPBY(date, MAP(startt, endt, LAMBDA(a,b, ARRAYTOTEXT(SEQUENCE(, INT((b - a) * 24), a * 24)))), ARRAYTOTEXT,, 0),, 1)), ", ", ";"), ""), LAMBDA(c, ARRAYTOTEXT(UNIQUE(c, TRUE))))), ", ", ";", TRUE), INT(dr)), ""), LAMBDA(z, ARRAYTOTEXT(SCAN(0, z, LAMBDA(a,b, IF(b = "", a, b))))))), ", ", ";"), LAMBDA(w, ARRAYTOTEXT(UNIQUE(w, TRUE))))), ", ", ";"), ""), COUNT) - 1) / 24 + MOD(dr, 1) / 24), {2,1,3})))Hope this helps.
IlirU
