I am trying to create a spreadsheet that will help me track overtime hours scheduled by 30 minute intervals. The goal is to determine how many people are working overtime during each specific interval. On the first worksheet I have the week laid out Sunday through Saturday with the option to enter in the employee's start and end time, as well as a column that will calculate the total amount of hours. This concept is simple enough for me to figure out.
The second worksheet is where I am having issues. I have the first row broken out Sunday through Saturday and the first column set to show each 30 minute interval (5:00am-11:00p). What I would like is if someone works on Sunday from 5:00am-7:00am (input on the first worksheet), the second worksheet would add a 1 to each interval they worked that day. This way if I have 5 people working on Sunday from 5:00am-7:00am it would show the number 5 on every 30 minute interval from 5:00am to 7:00am.
I tried using a COUNTIF function, but I can only figure how how to use it for a specific cell and not a range of times. I have attached some screenshots and the file to better show what I am trying to do. Hopefully this makes sense to someone.
Bookmarks