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]
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?