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]
Nice logic. As a LAMBDA function, it can be applied to the results of GROUPBY-THUNK to handle all supervisors:
= LET(
grp, GROUPBY(tblData[Supervisor], tblData[Date] + tblData[[Start Time]:[End Time]], THUNK, 0, 0,, tblData[Start Time] < tblData[End Time]),
HSTACK(DROP(grp,, -2), MAP(CHOOSECOLS(grp, -2), CHOOSECOLS(grp, -1), GroupOverlapHours))
)Where GroupOverlapHours is defined as:
= LAMBDA(startTime,endTime,
LET(
starts, EXPAND(startTime(),, 2, 1),
finish, EXPAND(endTime(),, 2, -1),
stack, SORT(VSTACK(starts, finish)),
headcount, SCAN(0, TAKE(stack,, -1), SUM),
event, VSTACK(TAKE(stack,, 1), TAKE(stack, -1, 1)),
duration, DROP(event, 1) - DROP(event, -1),
SUM(IF(headcount, duration)) * 24
)
)Alternative definition, using my VALS/EVALS functions:
= LAMBDA(startTime,endTime,
LET(
one, startTime(),
two, endTime(),
arr, SCAN(VALS3(), SORTBY(MAP(one, two, VALS2), one, 1, two, 1), LAMBDA(acc,val,
LAMBDA(beg,end,low,upp,lId, IF(AND(beg >= low, beg <= upp), VALS3(low, MAX(upp, end), lId), VALS3(beg, end, lId + 1)))(
val(1), val(2), acc(1), acc(2), acc(3))
)
),
grp, TAKE(GROUPBY(EVALS(arr, 3, 1), arr, LAMBDA(x, INDEX(x, ROWS(x), 1)), 0, 0),, -1),
SUM(EVALS(grp, 2, 1) - EVALS(grp, 1, 1)) * 24
)
)Additional resources: https://gist.github.com/RackofLambda
Hi David
That is truly impressive!
I probably should have taken it one step further to present the formula as a Lambda function, as you did with GroupOverlapHours. I am coming to think that practically every calculation should be held within a well-documented Lambda function. Your use of GROUPBY to marshal complex calculations is also impressive. Far from being the poor man's equivalent of the Pivot Table, GROUPBY and PIVOTBY appear to have hidden depths that allow them to go well beyond simple aggregations.
The use of thunks also presents some interesting choices. In some cases they appear to be used to insert fragments of code for conditional execution; other times it is more a matter of referencing precalculated values held within memory for repeated use.