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]
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 > Refresh
Notes:
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]