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!
Bookmarks