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 comments
Copilot_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