Tracking and Calculating Overtime

[neiltron]

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
	A	B	C	D	E	F	G	H
1	Date	Day	Employee	Title	Hours Worked	Straight Time	Overtime	TotalHours (Week)
2	8/8/2006	Tuesday	Employee1	LabAssistant	12	8	4	12
3	8/9/2006	Wednesday	Employee1	LabAssistant	12	8	4	24
4	8/10/2006	Thursday	Employee1	LabAssistant	12	8	4	36
5	8/11/2006	Friday	Employee1	LabAssistant	12	4	8	48
Sheet1

 

[big_mac]

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.

 

[orbea_adam]

Here you go:

Book1
	E	F	G	H
1	Hoursworked	Straighttime	OT	Total(week)
2	12	8	4	12
3	8	8	0	20
4	8	8	0	28
5	8	8	0	36
6	8	4	4	44
7	10	0	10	54
Sheet1

 

[neiltron]

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]

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]

I was off 1 column from my earlier post but same formula.

payroll hrs calculation.xls
	A	B	C	D	E	F	G	H	I
1		Date	Day	Employee	Title	Hours	Straight	Overtime	TotalHours
2	2	8/8/06	Tuesday	Employee1	LabAssistant	12	8	4	12
3	3	8/9/06	Wednesday	Employee1	LabAssistant	12	8	4	24
4	4	8/10/06	Thursday	Employee1	LabAssistant	12	8	4	36
5	5	8/11/06	Friday	Employee1	LabAssistant	12	4	8	48
6	6	8/8/06	Monday	Employee2	Frogcatcher	4	4	0	4
7	7	8/9/06	Tuesday	Employee2	Frogcatcher	12	8	4	16
8	8	8/10/06	Wednesday	Employee2	Frogcatcher	13	8	5	29
9	9	8/11/06	Thursday	Employee2	Frogcatcher	2	2	0	31
10	10	8/12/06	Friday	Employee2	Frogcatcher	14	9	5	45
11	11	8/8/06	Monday	Employee3	LabAssistant	10	8	2	10
12	12	8/9/06	Tuesday	Employee3	LabAssistant	19	8	11	29
13	13	8/10/06	Wednesday	Employee3	LabAssistant	13	11	2	42
14	14	8/11/06	Thursday	Employee3	LabAssistant	10	0	10	52
15	15	8/12/06	Friday	Employee3	LabAssistant	3	0	3	55
Sheet1

 

[orbea_adam]

payroll hrs calculation.xls
	A	B	C	D	E	F	G	H	I
1		Date	Day	Employee	Title	Hours	Straight	Overtime	TotalHours
2	2	8/8/06	Tuesday	Employee1	LabAssistant	12	8	4	12
3	3	8/9/06	Wednesday	Employee1	LabAssistant	12	8	4	24
4	4	8/10/06	Thursday	Employee1	LabAssistant	12	8	4	36
5	5	8/11/06	Friday	Employee1	LabAssistant	12	4	8	48
6	6	8/8/06	Monday	Employee2	Frogcatcher	4	4	0	4
7	7	8/9/06	Tuesday	Employee2	Frogcatcher	12	8	4	16
8	8	8/10/06	Wednesday	Employee2	Frogcatcher	13	8	5	29
9	9	8/11/06	Thursday	Employee2	Frogcatcher	2	2	0	31
10	10	8/12/06	Friday	Employee2	Frogcatcher	14	9	5	45
11	11	8/8/06	Monday	Employee3	LabAssistant	10	8	2	10
12	12	8/9/06	Tuesday	Employee3	LabAssistant	19	8	11	29
13	13	8/10/06	Wednesday	Employee3	LabAssistant	13	11	2	42
14	14	8/11/06	Thursday	Employee3	LabAssistant	10	0	10	52
15	15	8/12/06	Friday	Employee3	LabAssistant	3	0	3	55
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]

payroll hrs calculation.xls
	A	B	C	D	E	F	G	H	I
1		Date	Day	Employee	Title	Hours	Straight	Overtime	TotalHours
2	2	8/8/06	Tuesday	Employee1	LabAssistant	12	8	4	12
3	3	8/9/06	Wednesday	Employee1	LabAssistant	12	8	4	24
4	4	8/10/06	Thursday	Employee1	LabAssistant	12	8	4	36
5	5	8/11/06	Friday	Employee1	LabAssistant	12	4	8	48
6	6	8/8/06	Monday	Employee2	Frogcatcher	4	4	0	4
7	7	8/9/06	Tuesday	Employee2	Frogcatcher	12	8	4	16
8	8	8/10/06	Wednesday	Employee2	Frogcatcher	13	8	5	29
9	9	8/11/06	Thursday	Employee2	Frogcatcher	2	2	0	31
10	10	8/12/06	Friday	Employee2	Frogcatcher	14	8	6	45
11	11	8/8/06	Monday	Employee3	LabAssistant	10	8	2	10
12	12	8/9/06	Tuesday	Employee3	LabAssistant	19	8	11	29
13	13	8/10/06	Wednesday	Employee3	LabAssistant	13	8	5	42
14	14	8/11/06	Thursday	Employee3	LabAssistant	10	0	10	52
15	15	8/12/06	Friday	Employee3	LabAssistant	3	0	3	55
Sheet1

 

[big_mac]

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.