Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 38

Thread: Formula for tracking my hours worked including overtime etc

  1. #21
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    No, it doesn't make sense, I am not seeing what you are showing.

  2. #22
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi chichirod,
    is this help you ? Plz check.
    Attached Files Attached Files

  3. #23
    Hi sambit, its very close. However on the 5 shift (THU) the regular hours should automatically go to 8 in this example so that I hit the 44 regular hours for the week and then the 2 overtime shifts after that are correct. I will re-attach a file that shows the different scenario's. The first scenario reads perfect if every shift was a 14 hours shift, but when you change one of them in the second scenario it doesnt quite recalculate correct. In the third scenario I take a shift out all together (0 hours on the first day).
    Please look on the "Bob Phillips Formula" tab.

    I see that your formula and Bob's are quite different but the end result seems very close.
    Attached Files Attached Files
    Last edited by chichirod; 2015-03-30 at 03:28 PM.

  4. #24

    Different Scenario's

    Hi Bob I have put three different scenario's on the sheet. In the first scenario if everyday/shift worked was a 14 hour shift then it would be perfect, However in the second scenario I changed one of the shifts so that it is only 8 hours. In the third scenario I made one of the shifts 0 as if I never worked that day at all.
    Attached Files Attached Files

  5. #25
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    The foirmula works as yoiu want, change C2 to 1:00 PM and see.

    However, if the table does not start in row 1, you have to adjust this part of the formula

    SUM($D$1:$D1)

    to your table. So it becomes

    SUM($D$17:$D17)

    in your second table,

    SUM($D$33:$D33)

    in your thirs

  6. #26
    Hi Bob, When I change C2 to 1:00 pm it changes the (FRI) reg hours to 2 and overtime to 12. What I would like it to do is change (THU) Overtime to 6 Reg hours and 8 overtime. Essentially trying to get the 44 regular hours for the week in the earliest part of the week WITHOUT going over 10 hours.

  7. #27
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    That conflicts with what you said in post #3, ... On the fifth shift of the week the first 4 hours of the shift is Reg time and then everything else is OT.

  8. #28
    Sorry that is true if each of the first four shifts has at least 10 hours of regular time in them. The fifth shift would only be 4 hours. The our pay structure works is that we need to work 44 hours a week at regular time and then everything else is overtime. However they only count the first 10 hours of your shift as regular time on any given day. So on the 5 day you only have the last 4 hours to make up your 44 regular and then everything is overtime from there on. But this is where maybe it gets confusing, if you dont hit 10 regular hours in one of those first 4 shifts then you need to make it up on the 5 shift. On a really bad week for hours lets say we never work more than an 8 hour shift. To get to our 44 hours of regular time it would take us till the halfway point of our 6 shift before we hit any overtime at all.

  9. #29
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    So what happens if you do say 8,8,8,8,8 on the first 5 shifts and then 10 on the Fri. Is that 10 all OT, or does that rule go out the window as they have so far only done 40 hours?

  10. #30
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    A better example is 12,8,8,8,6, because Sun would get 2 hours OT, but they still only have 42 hours by Fri.

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

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