Tracking and Calculating Overtime

neiltron

New Member
Joined
Sep 7, 2006
Messages
3
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

big_mac

Board Regular
Joined
Jul 21, 2006
Messages
167
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.
 

neiltron

New Member
Joined
Sep 7, 2006
Messages
3
Having a problem with your solution, orbea_adam

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?
 

orbea_adam

Well-known Member
Joined
Nov 15, 2005
Messages
500

ADVERTISEMENT

Having a problem with your solution, orbea_adam

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
 

big_mac

Board Regular
Joined
Jul 21, 2006
Messages
167
I was off 1 column from my earlier post but same formula.
payroll hrs calculation.xls
ABCDEFGHI
1DateDayEmployeeTitleHoursStraightOvertimeTotalHours
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
 

orbea_adam

Well-known Member
Joined
Nov 15, 2005
Messages
500

ADVERTISEMENT

payroll hrs calculation.xls
ABCDEFGHI
1DateDayEmployeeTitleHoursStraightOvertimeTotalHours
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
 

big_mac

Board Regular
Joined
Jul 21, 2006
Messages
167
payroll hrs calculation.xls
ABCDEFGHI
1DateDayEmployeeTitleHoursStraightOvertimeTotalHours
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
 

big_mac

Board Regular
Joined
Jul 21, 2006
Messages
167
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.
 

Forum statistics

Threads
1,136,368
Messages
5,675,358
Members
419,565
Latest member
Phil57

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top