Forum Discussion
Calculate overlapping hours
Hi
To help people who search for existing solutions it would be smart of you to revise the title of this thread with Calculate non-overlapping hours ;-) - Thanks
With Power Query > Pivot Report:
Added dummy records to those you provided. Looks good to me...
#1 Please double-check and confirm (or not). Sample (to download) avail. at:
https://1drv.ms/x/c/1cd824d35610aacb/IQCkJLQ4qI2hQ6OG2-ntg7wiATHCsDR7AVWdpHB_O7bJjgs?e=xn9UML
#2 Let me know if you prefer a flat Table instead of a PivotTable Report
How to use:
With your data in TableData > Right-click somewhere in PivotTable Report > RefreshNotes:
1) If you double-click on a number in the [Total Hours] col. of the PivotTable Report a sheet will give you the details of what made that number. However, you might get less records than in TableData as the query filters out some records for perf. reason.
As an ex. : on 11/06/25 Freddie has 9 records but double-clicking on his 10.5 [Total Hours] for that date you get 8 records. The one that's filtered out is:because [Start Time] > [End Time]
2) Also filtered out are records, where for the same [Supervisor] on a given [Date], we have duplicated [Start Time] & [End Time]
3) With the provided sample dataset, [Supervisor] Foo isn't reported as it has only 1 record where [End Time] = [Start Time]
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