# Counting Scheduled Hours by Interval

1. ## Counting Scheduled Hours by Interval

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.

3. ## Re: Counting Scheduled Hours by Interval

Hey Jim28! So that seems to have the desired effect I need, except I noticed that the final hour (11:00pm) doesn't seem to count on the OT report. Also, great work! I wasn't sure if my explanation made sense but you nailed it!

4. ## Re: Counting Scheduled Hours by Interval

Hello
you can also use this one.

``Please Login or Register  to view this content.``
D3 = end time
C3- Start Time

5. ## Re: Counting Scheduled Hours by Interval

Try this formula in OT Report B2, copy down & across.

=COUNTIFS(INDEX('OT Tracker'!\$C\$3:\$W\$5000,0,MATCH(B\$1,'OT Tracker'!\$C\$1:\$W\$1,0)),"<="&\$A2,INDEX('OT Tracker'!\$C\$3:\$W\$5000,0,MATCH(B\$1,'OT Tracker'!\$C\$1:\$W\$1,0)+1),">="&\$A2)

6. ## Re: Counting Scheduled Hours by Interval

Hey Haseeb! That worked even better except it's counting 2 instead of 1.

7. ## Re: Counting Scheduled Hours by Interval

Would you tell where did you get 2 instead of 1?

If some one started at 07:00 & end at 11:00. So how do you want to count.

``Please Login or Register  to view this content.``
Is it right?

8. ## Re: Counting Scheduled Hours by Interval

I get 2 on the OT report worksheet. If I have someone work from 5am to 6pm they show up as follows:
5:00am - 2
5:30am - 2
6:00am - 2

Instead of counting 1 for every interval they work it counts 2.

9. ## Re: Counting Scheduled Hours by Interval

See the attached.

Also for validation list, define a name for PT Report A2:A38, then use this name in Validation.

10. ## Re: Counting Scheduled Hours by Interval

Hi GrumpyRogue glad I could help.

The 11:00pm interval will not count because if someone works to 11pm then the last 0.5 interval is from 10:30pm to 11:00pm.

Hope this makes sense.

11. ## Re: Counting Scheduled Hours by Interval

Hello Haseeb and Jim! I just wanted to let you know that both of your answers helped solve my problem! I doubt I would have figured this out on my own. Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1