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]
Provided Copilot (ChatGPT 5.1 mode) the code of the Power Query marked as solution and asked:
1) Find an alternative ==> Worked 1st time
2) Add test in case there's only 1 record per Date & Supervisor group
3) Add comments
Copilot_NoOverlap_v1:
// TableData
let
Source = Excel.CurrentWorkbook(){[Name="TableData"]}[Content],
FilteredTime = Table.SelectRows( Source, each
[End Time] > [Start Time]
),
ChangedTypes = Table.TransformColumnTypes( FilteredTime,
{ {"Volunteer Name", type text}, {"Date", type date}, {"Start Time", type time}, {"End Time", type time}, {"Supervisor", type text} }
)
in
ChangedTypes
// Copilot_NoOverlap_v1
let
Source = TableData, // Input table
Grouped = Table.Group( Source, {"Date", "Supervisor"}, // Group by Date + Supervisor
{"Hours", (tbl as table) as number =>
let
RowCount = Table.RowCount(tbl), // Count rows in group
Result =
if RowCount = 1 then // Single interval in group
Duration.TotalHours( tbl[End Time]{0} - tbl[Start Time]{0} ) // Safe because TableData filters invalid rows // Clamp negative durations to 0
else // Multi‑interval case
let
Starts = tbl[Start Time], // List of StartTime
Ends = tbl[End Time], // List of EndTime
TimeFlags = List.Combine( // Build Start(+1)/End(-1) events
{
List.Transform(Starts, each [Time = _, Flag = 1]), // Start event
List.Transform(Ends, each [Time = _, Flag = -1]) // End event
}
),
SortedTimeFlags = List.Sort( TimeFlags, // Sort by Time, End before Start
(a, b) =>
if a[Time] < b[Time] then -1
else if a[Time] > b[Time] then 1
else if a[Flag] < b[Flag] then -1
else 1
),
seed = [Active = 0, LastTime = null, Total = #duration(0,0,0,0)], // Initial accumulator state
TimelineState = List.Accumulate( SortedTimeFlags, seed, // Walk timeline and accumulate coverage
(state, current) =>
let
currentTime = current[Time], // Current event time
activeBefore = state[Active], // Active intervals before event
total =
if activeBefore > 0 and state[LastTime] <> null
then state[Total] + (currentTime - state[LastTime]) // Add covered duration
else state[Total], // No active coverage
newActive = activeBefore + current[Flag] // Update active count
in
[
Active = newActive, // New active count
LastTime = currentTime, // Update last timestamp
Total = total // Updated total duration
]
)
in
Duration.TotalHours(TimelineState[Total]) // Convert duration to hours
in
Result, // Return hours for this group
type number // Output type
}
),
SortedSupervisorByDate = Table.Sort( Grouped,
{{"Date", Order.Ascending}, {"Supervisor", Order.Ascending}}
)
in
SortedSupervisorByDate
To reduce dependency on Records (could be "slow" on large tables) :
1) Provided Copilot code to replace its initial steps with a Table (vs. Records) approach
2) Provided it directions to build function BuildFlagTable
3) Asked it to integrate everything and add commentsCopilot_NoOverlap_v2:
// TableData let Source = Excel.CurrentWorkbook(){[Name="TableData"]}[Content], FilteredTime = Table.SelectRows( Source, each [End Time] > [Start Time] ), ChangedTypes = Table.TransformColumnTypes( FilteredTime, { {"Volunteer Name", type text}, {"Date", type date}, {"Start Time", type time}, {"End Time", type time}, {"Supervisor", type text} } ) in ChangedTypes // CopilotNoOverlap_v2 let Source = TableData, // Build a Time/Flag table for a given column and flag BuildFlagTable = (tbl as table, columnName as text, flag as number) as table => let Renamed = Table.RenameColumns( Table.SelectColumns(tbl, {columnName}), {{columnName, "Time"}} ), // Extract and rename to 'Time' WithFlag = Table.AddColumn( Renamed, "Flag", each flag, Int64.Type ) // Add flag (+1 or -1) in WithFlag, // Build combined Start(+1) and End(-1) table BuildTimeFlagsTable = (tbl as table) as table => let Starts = BuildFlagTable(tbl, "Start Time", 1), // Start events Ends = BuildFlagTable(tbl, "End Time", -1) // End events in Table.Combine({Starts, Ends}), // Append both tables Grouped = Table.Group( Source, {"Date", "Supervisor"}, // Group by Date + Supervisor {"Hours", (tbl as table) as number => let RowCount = Table.RowCount(tbl), // Count rows in group Result = if RowCount = 1 then // Single interval in group Duration.TotalHours( tbl[End Time]{0} - tbl[Start Time]{0} ) // Safe due to TableData filter else // Multi‑interval case let TimeFlagsTable = BuildTimeFlagsTable(tbl), // Build Start/End events SortedTimeFlagsTable = Table.Sort( TimeFlagsTable, { {"Time", Order.Ascending}, {"Flag", Order.Descending} } ), // Sort by Time, End before Start SortedTimeFlags = Table.ToRecords(SortedTimeFlagsTable), // Convert to list of records seed = [Active = 0, LastTime = null, Total = #duration(0,0,0,0)], // Initial accumulator TimelineState = List.Accumulate( SortedTimeFlags, seed, (state, current) => let currentTime = current[Time], // Current event time activeBefore = state[Active], // Active intervals before event total = if activeBefore > 0 and state[LastTime] <> null then state[Total] + (currentTime - state[LastTime]) // Add covered duration else state[Total], // No active coverage newActive = activeBefore + current[Flag] // Update active count in [ Active = newActive, // New active count LastTime = currentTime, // Update last timestamp Total = total // Updated total duration ] ) in Duration.TotalHours(TimelineState[Total]) // Convert duration to hours in Result, // Return hours for this group type number } ), SortedSupervisorByDate = Table.Sort( Grouped, {{"Date", Order.Ascending}, {"Supervisor", Order.Ascending}} ) in SortedSupervisorByDateUpdated workbook at: https://1drv.ms/x/c/1cd824d35610aacb/IQCkJLQ4qI2hQ6OG2-ntg7wiATHCsDR7AVWdpHB_O7bJjgs?e=akf0vp