Calculate overlapping hours

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.