Formula for tracking my hours worked including overtime etc

No, it doesn't make sense, I am not seeing what you are showing.
 
Hi chichirod,
is this help you ? Plz check.
 

Attachments

  • TEST.xlsx
    9 KB · Views: 9
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.
 

Attachments

  • 2015 Tracking Hours Sheet2.xlsx
    66.9 KB · Views: 5
Last edited:
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.
 

Attachments

  • 2015 Tracking Hours Sheet2.xlsx
    66.9 KB · Views: 10
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
 
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.
 
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.
 
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.
 
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?
 
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.
 
so with the 8,8,8,8,8,10.
The 8's would all be just regular time. The 10 on the FRI would be the first 4 hours as regular then the rest would be Overtime. Because now the 44 hour rule for the week kicks in
 
Last edited:
The 12,8,8,8,6.
So the first shift (12) would be 10 reg and 2 OT. All 3 of the (8) would be regular and the (6) would also be regular. Since the 12 hour shift was over the 10 hour rule the OT would kick in. With the 8 and 6 hour shifts none were over the 10 hour rule and did not surpass the 44 hours for the week so none of them would qualify for OT
 
Last edited:
So are you saying that the rule you stated at the beginning that Fri and Sat is all OT does not really apply?

I have to ask, if they do a week of 10,8,8,8,8,0,0 why would they get 2 hours OT on Sun when they fail to do 44 hours in the week?
 
Sorry I did a poor job of explaining the FRI, SAT thing. My example was showing what a full week of hours would look like.

With the 10,8,8,8,8,0,0 scenario none of that would be OT. Because none of it went over 10 hours and it never met the 44 hour rule for the week.

I'm not sure where in here that I said it did.

Had that scenario looked like this 12,8,8,8,8,0,0 then the first shift (SUN) there would have been 10 regular hours and 2 OT hours and the rest of the week would have still just been regular hours because the 44 hour rule never kicks in.

Well it just actually gets to the 44 hours so had there been any hours in one of those "0" positions then that would have been straight OT
 
Last edited:
Sorry my last line in my last statement is NOT correct (Well it just actually gets to the 44 hours so had there been any hours in one of those "0" positions then that would have been straight OT). Because out of that first 12 hour shift only 10 of them would have counted towards the 44 hours for the week.
 
My Final attempt

SUN row : =MIN(($C1-$B1)*24,10)
MON-SAT rows: =IF(OR($A2="FRI",$A2="SAT"),MIN(44-SUM($D$1:$D1),($C2-$B2)*24),IF($A2="THU",MIN(44-SUM($D$1:$D1),($C2-$B2)*24),MIN(10,($C2-$B2)*24)))

Remember to adjust the SUM values if using in rows other than 1-7.
 
It looked like it was working until i put no hours in for Sunday as if I didn't work it. I have all of the days as 14 hour shifts. So what it did was changed the Thursday shift to 14 regular hours and no OT instead of making it a 10 reg and 4 OT and then making Friday 4 OT and 10 reg. I do really thank you for trying. I know you put way more work into this than you had to. Thank you
 
You must log in or register to reply here.
Back
Top