Sum hours worked between time frame based on punch times and criteria

First post here; hello everyone,

I am stumped and need some help from you guys. I posted on Stack and Reddit with no help from anyone other than "use SUMIFS" or "use Pivot Table" which does not help me at all given I can't seem to understand how those could even work with my dataset.

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

orQfg.png

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

ximhJ.png

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 20+ 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 or maybe even a pivot table solution?

EDIT: The first record would be recorded as 1 in each cell from I12:I19.

Thanks!