Forum Discussion

volunteersfpl's avatar
volunteersfpl
Copper Contributor
Dec 17, 2025
Solved

Calculate overlapping hours

Hello,  I need to report how many hours a staff member supervised one or more volunteers. For a very simplified example, Volunteer Name Date Start Time  End Time  Supervisor ...
BI & Data Analysis
excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Dec 18, 2025

    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]

Lorenzo's avatar
Lorenzo
Silver Contributor
Jan 09, 2026

Another Power Query alternative adapted from a solution (https://stackoverflow.com/questions/79487991/combine-rows-if-the-dates-exist-in-another-row) developed by Ron Rosenfeld - Credit OliverScheurich​ for finding that case

// 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

// RonRosenfeld_NoOverlap
// Adapted from solutiuon posted by Ron Rosenfeld:
//     https://stackoverflow.com/questions/79487991/combine-rows-if-the-dates-exist-in-another-row
let
    Source = TableData,
    SelectedColumns = Table.SelectColumns( Source,
        {"Supervisor", "Date", "Start Time", "End Time"}
    ),
    // Group by Date & Supervisor
    NoOverlapBySupervisorDate = Table.Group( SelectedColumns, {"Date", "Supervisor"},
        
        // Add [Shifted End Time] column to each sorted subgroup  
        {"Shifted", (t) =>
            let
              RemovedColumns = Table.RemoveColumns( t, {"Date", "Supervisor"} ),
              SortedStartTime = Table.Sort( RemovedColumns, {"Start Time",Order.Ascending} ),
              ShiftedEndTime = Table.FromColumns(
                  Table.ToColumns( SortedStartTime ) &
                  { {null} & List.RemoveLastN( Table.Column( SortedStartTime,"End Time"), 1 ) },
                  type table [Start Time = time, End Time = time, Shifted End Time = nullable time]
              ),

              // Overlap exists if [Start time] is less than the [Shifted End time]
              TimeOverlaps = Table.AddColumn( ShiftedEndTime, "Overlap", each
                [Start Time] < [Shifted End Time], type logical
              ),

              // First row in [Overlap] column will be null so we fill up from the second row to see if they overlap
              FilledUpOverlap = Table.FillUp( TimeOverlaps, {"Overlap"} )
            in
              // Group each subtable by the logical overlap
              Table.Group( FilledUpOverlap, "Overlap",
                {
                  {"Start Time", each List.Min( [Start Time] ), type time},
                  {"End Time", each List.Max( [End Time] ), type time}
                },
                GroupKind.Local,
                (x, y) => Number.From( y <> true )
              ),
            type table [Overlap = logical, Start Time = time, End Time = time]
        }
   ),
    ExpandedTimeColumns = Table.ExpandTableColumn( NoOverlapBySupervisorDate, "Shifted",
      {"Start Time", "End Time"}
    ),
    AddedHours = Table.AddColumn(ExpandedTimeColumns, "Hours", each
      Duration.TotalHours( [End Time] - [Start Time] ), type number
    )
in
    AddedHours

Updated workbook avail. at https://1drv.ms/x/c/1cd824d35610aacb/IQCkJLQ4qI2hQ6OG2-ntg7wiATHCsDR7AVWdpHB_O7bJjgs?e=xn9UML

Resources