Formula for tracking my hours worked including overtime etc

chichirod

New member
Joined
Mar 26, 2015
Messages
20
Reaction score
0
Points
0
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.
 
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))
 

Attachments

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

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))
 
Hi,
Plz see the file.
 

Attachments

  • TEST.xlsx
    8.6 KB · Views: 14
WOW Thank you

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
 
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
 
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
 
Hi chichirod,
sorry! I was not looking throughly, I have changes the previous post file. And also working current requirement. plz check.
 

Attachments

  • TEST.xlsx
    9.9 KB · Views: 14
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
 
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"
 
Hi Bob, I sent you a PM. I would like to send you my file. Not sure how to just attach here
 
Reply to the thread, click the 'Go Advanced' button, there is a button there below the response box to 'Manage Attachments'
 
Hi Bob, this is the file im trying to work on, your formula is on the second tab at the bottom and a formula that "sambit" is working on. Just to prove how much of a noobie I am I had password protected the sheet so if it requires it when you go to open it the pass word is"07231971"
 

Attachments

  • 2015 Tracking Hours Sheet.xlsx
    72 KB · Views: 4
The sheet im working on

Hi, this is the sheet im working on and the tabs at the bottom are from yourself and another gentleman that is helping me with this formula. Maybe this will explain better.
 

Attachments

  • 2015 Tracking Hours Sheet.xlsx
    72 KB · Views: 6
It's password protected.
 
In the formula that I gave you in post #9, just change the THU to THUR.
 
Sorry cant believe I did that. Although this isn't a huge deal the formula definitely works, if you were to say show no hours on one of the first 4 days then would it be possible for it to redo the cells so that for example
SUN0.00.0
MON5:00 AM7:00 PM10.04.0
TUE5:00 AM7:00 PM10.04.0
WED5:00 AM7:00 PM10.04.0
THU5:00 AM6:00 PM4.09.0
FRI5:00 AM6:00 PM10.03.0
SAT5:00 AM9:00 PM0.016.0


SO THAT MON, TUE, WED, THU SHOW 10 HOURS IN THE REG COLUMN
FRI SHOWS 4 HOURS IN THE REG COLUMN AND THE REST OT
SAT WOULD BE STRAIGHT OT
 
Isn't that what it returns?
 
It does get to the 44 total regular hours. I will re-attach the file and you will be able to see, I keyed 0 hours in on the first day of the week and then 14 hour shifts for the rest of the week.
SUN0.00.0
MON5:00 AM7:00 PM10.04.0
TUE5:00 AM7:00 PM10.04.0
WED5:00 AM7:00 PM10.04.0
THU5:00 AM7:00 PM4.010.0
FRI5:00 AM7:00 PM10.04.0
SAT5:00 AM7:00 PM0.014.0
So you can see it shows the thu shift as 4 reg and then 10 OT.
Ideally the last shift of the week before I start into straight OT should be the shift that is making up the last few hours of regular time to get to the 44. Does that make sense.
 

Attachments

  • 2015 Tracking Hours Sheet.xlsx
    72 KB · Views: 10
Back
Top