# Tracking and Calculating Overtime

#### neiltron

##### New Member
On a daily basis, I keep track of the hours employees work. I am trying to set up a sheet that will calculate overtime hours for any hours worked over 8 in one day, and any over 40 in the week.

I need to set up a sheet that will calculate the straight and overtime values based on how many hours are worked each day rather than manually calculating them myself.

Do you guys have any ideas on how I could do this?
Book1
ABCDEFGH
1DateDayEmployeeTitleHours WorkedStraight TimeOvertimeTotalHours (Week)
28/8/2006TuesdayEmployee1LabAssistant128412
38/9/2006WednesdayEmployee1LabAssistant128424
48/10/2006ThursdayEmployee1LabAssistant128436
58/11/2006FridayEmployee1LabAssistant124848
Sheet1

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Copy & paste these formulas in the following cells
F2
=IF(\$D3=\$D2,IF(\$I2>40,0,IF(\$I2+\$F3>40,\$F3-(\$I2+\$F3-40),IF(\$F3>8,8,\$F3))),IF(\$F3>8,8,\$F3))
G2
=\$F3-\$G3
H2
=SUM(\$G3:\$H3)+IF(\$D3=\$D2,\$I2,0)
you should be able to paste to the cells below each of these.

I have one employee down as working 13 hours one day. He had a total of 28 hours the previous day. He has a total of 41 hours. It's calculating his straight time as 12 hours and OT as 1. Should be 8 hours of straight time 5 of OT. How can I set a maximum of 8 hours possible for the straight time cell and have the OT cell make up for it?

I have one employee down as working 13 hours one day. He had a total of 28 hours the previous day. He has a total of 41 hours. It's calculating his straight time as 12 hours and OT as 1. Should be 8 hours of straight time 5 of OT. How can I set a maximum of 8 hours possible for the straight time cell and have the OT cell make up for it?
You're right. The design doesn't work when an employee works over 12 hours in a day and the total exceeds 40, let me tinker some more

I was off 1 column from my earlier post but same formula.
payroll hrs calculation.xls
ABCDEFGHI
228/8/06TuesdayEmployee1LabAssistant128412
338/9/06WednesdayEmployee1LabAssistant128424
448/10/06ThursdayEmployee1LabAssistant128436
558/11/06FridayEmployee1LabAssistant124848
668/8/06MondayEmployee2Frogcatcher4404
778/9/06TuesdayEmployee2Frogcatcher128416
888/10/06WednesdayEmployee2Frogcatcher138529
998/11/06ThursdayEmployee2Frogcatcher22031
10108/12/06FridayEmployee2Frogcatcher149545
11118/8/06MondayEmployee3LabAssistant108210
12128/9/06TuesdayEmployee3LabAssistant1981129
13138/10/06WednesdayEmployee3LabAssistant1311242
14148/11/06ThursdayEmployee3LabAssistant1001052
15158/12/06FridayEmployee3LabAssistant30355
Sheet1

payroll hrs calculation.xls
ABCDEFGHI
228/8/06TuesdayEmployee1LabAssistant128412
338/9/06WednesdayEmployee1LabAssistant128424
448/10/06ThursdayEmployee1LabAssistant128436
558/11/06FridayEmployee1LabAssistant124848
668/8/06MondayEmployee2Frogcatcher4404
778/9/06TuesdayEmployee2Frogcatcher128416
888/10/06WednesdayEmployee2Frogcatcher138529
998/11/06ThursdayEmployee2Frogcatcher22031
10108/12/06FridayEmployee2Frogcatcher149545
11118/8/06MondayEmployee3LabAssistant108210
12128/9/06TuesdayEmployee3LabAssistant1981129
13138/10/06WednesdayEmployee3LabAssistant1311242
14148/11/06ThursdayEmployee3LabAssistant1001052
15158/12/06FridayEmployee3LabAssistant30355
Sheet1
big_mac,
You can't have more than 8 straight hours in a single day. That's the same problem I'm having

payroll hrs calculation.xls
ABCDEFGHI
228/8/06TuesdayEmployee1LabAssistant128412
338/9/06WednesdayEmployee1LabAssistant128424
448/10/06ThursdayEmployee1LabAssistant128436
558/11/06FridayEmployee1LabAssistant124848
668/8/06MondayEmployee2Frogcatcher4404
778/9/06TuesdayEmployee2Frogcatcher128416
888/10/06WednesdayEmployee2Frogcatcher138529
998/11/06ThursdayEmployee2Frogcatcher22031
10108/12/06FridayEmployee2Frogcatcher148645
11118/8/06MondayEmployee3LabAssistant108210
12128/9/06TuesdayEmployee3LabAssistant1981129
13138/10/06WednesdayEmployee3LabAssistant138542
14148/11/06ThursdayEmployee3LabAssistant1001052
15158/12/06FridayEmployee3LabAssistant30355
Sheet1

I am awful at the HTML creating; If we get this right the next step is create a VBA function. To get rid of the long formula.

Replies
7
Views
194
Replies
1
Views
871
Replies
22
Views
661
Replies
5
Views
485
Replies
4
Views
279

1,220,986
Messages
6,157,229
Members
451,407
Latest member
vdaesety

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back