Forum Discussion

volunteersfpl's avatar
volunteersfpl
Copper Contributor
Dec 17, 2025

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
mathetes's avatar
mathetes
Gold Contributor
Dec 18, 2025

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. 

  • volunteersfpl's avatar
    volunteersfpl
    Copper Contributor
    Dec 18, 2025

    Yes, the real-world situation is 300+ volunteers entering shifts across 3 summer months at 28 locations with 30-40 supervisors, and shifts aren't necessarily adjacent, so Lucy might supervise 2 volunteers from 10:00-noon, 1 volunteer from 11:30am-1pm, and then have an hour off and then have volunteers in from 3:00-5:00pm, so just taking MIN to MAX (10am-5pm) wouldn't be accurate. Some volunteers come in every day, some only once a week, but by the end of the summer, I have upwards of 10,000 rows of time entries. 

    I have had to just report volunteer hours, but now I need to report how many staff hours were spent supervising volunteers, so I would change how time is logged to have start and end time, instead of total hours. 

    This is one month of time entries at one location. Not difficult to go through and manually calculate, but time consuming at scale. 

    Volunteer Name    Date    Start Time     End Time     Supervisor
    G    5/17/2025    10:00 AM    1:15 PM    2
    L    5/17/2025    10:00 AM    12:00 PM    1
    O    6/6/2025    1:00 PM    2:00 PM    2
    O    6/9/2025    2:00 PM    4:00 PM    2
    W    6/9/2025    3:00 PM    5:00 PM    2
    T    6/10/2025    10:00 AM    12:00 PM    2
    I    6/11/2025    10:00 AM    12:00 PM    3
    S    6/11/2025    1:00 PM    3:00 PM    1
    T    6/11/2025    2:00 PM    4:00 PM    3
    T    6/12/2025    11:00 AM    1:00 PM    2
    Z    6/12/2025    11:00 AM    5:00 PM    3
    B    6/13/2025    2:00 PM    4:00 PM    2
    D    6/13/2025    1:00 PM    3:00 PM    2
    N    6/13/2025    2:00 PM    4:00 PM    2

    (wouldn't publish copied table so sorry for lack of formatting)

    • IlirU's avatar
      IlirU
      Brass Contributor
      Dec 18, 2025

       

      Hi volunteersfpl,

      Does the following formula meet your requirements? The formula works in Excel 365.

       

      =LET(
 table, A1:D15,
     d, DROP(table, 1), date, CHOOSECOLS(d, 2),
startt, CHOOSECOLS(d, 3), endt, CHOOSECOLS(d, 4),
    dr, DROP(GROUPBY(date, endt, MAX,, 0) * 24,, 1),
        VSTACK(HSTACK(TAKE(TAKE(table, 1),, 2), "Hours"),
        CHOOSECOLS(HSTACK(GROUPBY(date, CHOOSECOLS(d, 1), ARRAYTOTEXT,, 0),
        (BYROW(IFNA(--TEXTSPLIT(TEXTJOIN(";",, BYROW(--TEXTSPLIT(TEXTJOIN(";",,
        BYROW(IFNA(HSTACK(--TEXTSPLIT(TEXTJOIN(";",, BYROW(IFNA(--TEXTSPLIT(TEXTJOIN(";",,
        DROP(GROUPBY(date, MAP(startt, endt,
        LAMBDA(a,b, ARRAYTOTEXT(SEQUENCE(, INT((b - a) * 24), a * 24)))),
        ARRAYTOTEXT,, 0),, 1)), ", ", ";"), ""), LAMBDA(c, ARRAYTOTEXT(UNIQUE(c, TRUE))))),
        ", ", ";", TRUE), INT(dr)), ""), LAMBDA(z, ARRAYTOTEXT(SCAN(0, z, LAMBDA(a,b,
        IF(b = "", a, b))))))), ", ", ";"), LAMBDA(w, ARRAYTOTEXT(UNIQUE(w, TRUE))))),
        ", ", ";"), ""), COUNT) - 1) / 24 + MOD(dr, 1) / 24), {2,1,3})))

       

      Hope this helps.

      IlirU

Resources