# Thread: Formula for tracking my hours worked including overtime etc

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

2. Hi chichirod,

3. 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.

4. ## 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.

5. 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)

SUM(\$D\$33:\$D33)

6. 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. 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. 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. 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. 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 First 1 2 3 4 Last

#### Posting Permissions

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