+ Reply to Thread
Results 1 to 11 of 11

Counting Scheduled Hours by Interval

  1. #1
    Registered User
    Join Date
    12-03-2011
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    15

    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.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by grumpyrogue; 12-06-2011 at 09:23 PM.

  2. #2
    Forum Contributor
    Join Date
    06-24-2006
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    127

    Re: Counting Scheduled Hours by Interval

    The attached may help you... let me know
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-03-2011
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    15

    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. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    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
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    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)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  6. #6
    Registered User
    Join Date
    12-03-2011
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Counting Scheduled Hours by Interval

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

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    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. #8
    Registered User
    Join Date
    12-03-2011
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    15

    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. #9
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    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.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    06-24-2006
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    127

    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. #11
    Registered User
    Join Date
    12-03-2011
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    15

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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