Calculate overlapping hours

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

This has a limitation of only addressing data for a single supervisor.

= LET(
    starts,    EXPAND(startTimes,,2,1),
    finish,    EXPAND(endTimes,,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))
  )

It builds a table that has a row for every event (volunteer arrivals and departures) and accumulates the headcount to determine which intervals are paid.

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

I understand pivot tables a bit, so I'm going to go with Lorenzo​ Lorenzo's solution, but will also copy IlirU​'s code for reference--thanks to you both!

mathetes​, for my use, there is no deeper purpose. There is a yearly report that requires an entry for how many staff hours were spent supervising volunteers, so I'm just looking for a way to report good-quality data to meet that requirement. When I set up this summer's time reporting form, volunteers will report precise times, I just rounded to 15 minutes for dummy-data purposes.  

It looks to me as if Lorenzo​ has given you an effective answer. I was about to ask some more defining questions, which I will do anyway, in case his response doesn't work for you (if, for example, Power Query is a feature you haven't used and don't understand; no shame in that: I understand it, but have never used it myself).

My questions--and I'm writing as a person who at one time was the director of the HR & compensation database for a major corporation, so these are questions I would have asked at the time of a management request for a report on time spent--get at the deeper purpose being served. Let me first refer you back to my initial response, where I asked these questions:

• Are they only paid for time supervising?
• Are they paid more for time supervising than time spent at work but not supervising?
• If they're paid a straight hourly (or salary) regardless of whether time is spent supervising, do you still need to know specifically the amount of time spent supervising? Why? How is it used?

Part of what these questions are trying to get at is this: How precise do you really need to be? You're already (it would appear) rounding times to the nearest quarter hour. Which means your total times might well deviate from reality by significant margin. Does that matter?   To ask it again: How precise do you really need to be? Would it, for example, be just as valuable, maybe more so(!), to know that Susy routinely supervises two or three people a day (regardless of hours), while Larry rarely supervises more than one? And to repeat the question above, are these staff members tracking their own time--are they paid hourly or on salary--and are they compensated differently for time spent supervising? 

How is this summary data on hours spent supervising used? What difference does it make in your management of these staff people?

Yes, it’s possible, but it takes a small setup. The idea is to combine all time ranges for the same date and supervisor, then calculate the total span without double-counting overlaps. In your example, Lucy’s supervision runs from 1:00pm to 4:30pm, which equals 3.5 hours. You can do this in Excel by finding the earliest start time and latest end time for each supervisor and date, then subtracting the two. This works well as long as there are no gaps in supervision time.

Given that example, it's incredibly easy to calculate the 3.5 hours. Simply subtract the minimum of Lucy's start times from the maximum of her end times.

(making assumptions about which columns contain the relevant data),
time spent supervising =MAX(D2:D3)-MIN(C2:C3)    

Granted, that doesn't deal with other staff members and other days; just wanted to make the point that a simple example doesn't require much complications in solving.

But as you point out in asking the question, it's a very simplified example. Give us a more typical full day, complete with some examples that make it complicated. Along the way, can you explain the rules here that govern Lucy's (and other's) staff compensation, in particular

• Are they only paid for time supervising?
• Are they paid more for time supervising than time spent at work but not supervising?
• If they're paid a straight hourly (or salary) regardless of whether time is spent supervising, do you still need to know specifically the amount of time spent supervising? Why? How is it used?

By the way, you should expect it to be possible, whatever it is. Excel is very powerful; the challenge often is getting the problem clearly set forth and articulated.