Sum hours worked between multiple time frames based on punch times and criteria

Question

I am stumped and need some help from you guys.

I have a list of Punch Times (Sheet2) with their respective Departments:

I'm trying to figure out a way to get the data summed/consolidated into this format (Sheet1):

The solution for C5 would sum the total number of hours worked between 12 AM and 1 AM (using Punch Times on Sheet2 Col G & I) on Sundays (Sheet2 Col K) for Department 300 (Sheet2 Column R) stated in Sheet1 B2.

Note: Punch Out Time cannot go past 12 AM. If they work past 12 AM a new record will start underneath with 12 AM as the Punch In Time.

I can write a formula to grab the total time for each individual record and time frame, but I don't want to add a bunch of columns to the dataset and rather sum all the records in one cell (Sheet1 C5).

Is there any way to do this with a single formula?

Thanks!

Can you please clarify further? What would my sum range be in this case? — spacej3di
Agreed, SUMIFS is the easiest formula-based solution, however I recommend going with a Pivot Table instead — Fernando J. Rivera
Can you help direct me to a Pivot based solution? I don't see how I can take Punch Times and convert them to hours worked for specific times of the day. — spacej3di
It's unclear how you want to read each record. Say for example your first record in your table, where you have 8 hours worked on saturday starting at 7 am. Do you want to return an 8 in cell I12 or do you want to spread those 8 hours worked on the range I12:I19? If it's the latter it becomes a bit more complicated. — Fernando J. Rivera

Tom Sharpe Tom Sharpe · Accepted Answer · 2020-12-16T18:38:43

So here is the overlap formula for two date ranges adapted for the current scenario using time differences:

=SUM((Sheet1!$R$2:$R$4=$B$2)*(Sheet1!$L$2:$L$4=C$4)*TEXT((IF(Sheet1!$J$2:$J$4<$B5,Sheet1!$J$2:$J$4,$B5)-IF(Sheet1!$H$2:$H$4>$A5,Sheet1!$H$2:$H$4,$A5))*24,"general;\0"))

Must be entered as an array formula (pre-O365) using CtrlShiftEnter

Sheet1 contains

and Sheet2 contains

Sum hours worked between multiple time frames based on punch times and criteria

1 Answers