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

1. ## Formula for tracking my hours worked including overtime etc

If anyone is able to create a formula for me that would be able to calculate how many hours are regular time up to a max and how many hours are at overtime. I have a sheet that calculates most things for me already but I would like to be able to just key in for example that I started at 5:30 am and finished at 8:30pm and the formula would be able to figure out what was regular time and what was overtime. Theres more details than this to the formula but this is basically what im trying to find.

2. Start Time in Cell A2, Finish Time in Cell B2 and Assume Regular Time is 8 Hrs. Try below formula.

C2 Cell is Regular Time =IF(((B2-A2)*24)>8,8,((B2-A2)*24))
D2 Cell is Over Time =IF(((B2-A2)*24)>8,((B2-A2)*24-8))

3. ## Thats awesome thank you

Thank you, actually our regular hours are 10 hours then anything over 10 is over time. However its a bit more tricky than that as well. i'll try to explain.
For the first 4 days of the week, the first 10 hours of each shift is regular time, then everything after that initial 10 is over time.
On the fifth shift of the week the first 4 hours of the shift is Reg time and then everything else is OT.
On the 6 and 7 shift of the week it straight overtime
eg.
Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
Monday 5:00 am-7:00 pm = 10 reg and 4 OT
Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
Thursday 5:00 am-7:00 pm = 4 reg and 10 OT
Friday 5:00 am-7:00 pm = 14 OT
Saturday 5:00 am-7:00 pm = 14 OT

I would gladly pay you if you can figure this out.

[QUOTE=sambit;17947]Start Time in Cell A2, Finish Time in Cell B2 and Assume Regular Time is 8 Hrs. Try below formula.

C2 Cell is Regular Time =IF(((B2-A2)*24)>8,8,((B2-A2)*24))
D2 Cell is Over Time =IF(((B2-A2)*24)>8,((B2-A2)*24-8))

4. Hi,
Plz see the file.

5. ## WOW Thank you

Originally Posted by sambit
Hi,
Plz see the file.

Hi Sambit, im just reviewing your formula. WOW I seriously need to take a class on this. However there is one problem. Monday doesnt seem to work, When I change the times on tuesday it changes the calculated hours on monday. It seems to do this the rest of the way down the week. also lets say if I didnt work a 10 hour shift on one of those first few days would it know to make the THURSDAY for example more regular hours until I hit the 44 reg hours before it put the rest into Overtime.

Sorry it seems like im being pciky Im definately not and I do very much appreciate the time and effort you put into this, as I said before I would gladly pay you. Thanks

6. Regular hours

=IF(OR(\$A2="FRI",\$A2="SAT"),0,IF(\$A2="THU",MIN((\$C2-\$B2)*24,4),MIN((\$C2-\$B2)*24,10)))

Overtime hours

=(\$C2-\$B2)*24-\$D2

7. Originally Posted by Bob Phillips
Regular hours

=IF(OR(\$A2="FRI",\$A2="SAT"),0,IF(\$A2="THU",MIN((\$C2-\$B2)*24,4),MIN((\$C2-\$B2)*24,10)))

Overtime hours

=(\$C2-\$B2)*24-\$D2
Could I ask for another revision to this if possible. We do a lot of 15-16 hour days. But the way it works is the first 10 hours of your first 4 shifts are regular time. Anything over that 10 hours is overtime. On the fifth shift the first 4 hours is regular time then everything after that is Overtime. On the 6 and 7 shift everything is straight overtime. However if at some point in the first few shift we dont do our 10 hours of reg time then it pushs everything else back so essential we would have to make it up on the fifth shift where the 4 hours of regular time are before the overtime would kick in.

eg.
Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
Monday 5:00 am-7:00 pm = 10 reg and 4 OT
Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
Thursday 5:00 am-7:00 pm = 4 reg and 10 OT
Friday 5:00 am-7:00 pm = 14 OT
Saturday 5:00 am-7:00 pm = 14 OT

But if we didnt do a full 10 on one of those earlier shifts this is how it would look:
eg.
Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
Monday 5:00 am-12:00 pm = 7 reg and 0 OT
Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
Thursday 5:00 am-7:00 pm = 7 reg and 7 OT
Friday 5:00 am-7:00 pm = 14 OT
Saturday 5:00 am-7:00 pm = 14 OT

So before we just hit the straight overtime hours on day 6 and 7 we must complete the 44 regular hours.

Thank you for your time and the formula so far

8. Hi chichirod,
sorry! I was not looking throughly, I have changes the previous post file. And also working current requirement. plz check.

9. Originally Posted by chichirod
Could I ask for another revision to this if possible. We do a lot of 15-16 hour days. But the way it works is the first 10 hours of your first 4 shifts are regular time. Anything over that 10 hours is overtime. On the fifth shift the first 4 hours is regular time then everything after that is Overtime. On the 6 and 7 shift everything is straight overtime. However if at some point in the first few shift we dont do our 10 hours of reg time then it pushs everything else back so essential we would have to make it up on the fifth shift where the 4 hours of regular time are before the overtime would kick in.

eg.
Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
Monday 5:00 am-7:00 pm = 10 reg and 4 OT
Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
Thursday 5:00 am-7:00 pm = 4 reg and 10 OT
Friday 5:00 am-7:00 pm = 14 OT
Saturday 5:00 am-7:00 pm = 14 OT

But if we didnt do a full 10 on one of those earlier shifts this is how it would look:
eg.
Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
Monday 5:00 am-12:00 pm = 7 reg and 0 OT
Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
Thursday 5:00 am-7:00 pm = 7 reg and 7 OT
Friday 5:00 am-7:00 pm = 14 OT
Saturday 5:00 am-7:00 pm = 14 OT

So before we just hit the straight overtime hours on day 6 and 7 we must complete the 44 regular hours.

Thank you for your time and the formula so far
The regular hours for Sun stays as is, the overtime hours for all stays as is, but change the Mon formula to

=IF(OR(\$A3="FRI",\$A3="SAT"),IF(SUM(\$D\$2:\$D2)>=44,0,MIN(44-SUM(\$D\$2:\$D2),(\$C3-\$B3)*24)),IF(\$A3="THU",MIN((\$C3-\$B3)*24,4),MIN((\$C3-\$B3)*24,10)))

and copy that down for Tue-Sat

10. Hi Sambit,
It seems really close but for eg. if I work less than 10 reg hours on any of those first shifts than I need to make up the difference on the fifth until I hit 44 reg hours before I can start the straight overtime hour shifts. Would it also be possible for it not read false in the overtime column on days where I do less than 10 hours. Just have it read "0"

Page 1 of 4 1 2 3 ... 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
•