Calculating Overtime

[jefraz]

I am trying to create a spreadsheet that calculates overtime.

	Day1	Day2	Day 3	Day 4	Day 5	Day 6	Day 7
Employee Hours	8	9	8	10	9	5	0
Total Hours	8	17	25	35	44	49	49
Overtime	0	0	0	0	4	9	9

<tbody>
</tbody>

I was wondering if there was a formula or combination of formulas that would calculate this for me. On day 5 the employee went into overtime (over 40 hours) and should be paid 1.5 times there rate of pay. For example the employee normally makes $12 an hour. On days 1-4 they would receive $12 times their hours worked. On day 5 they go into overtime (over 40 hours) so they would get 5 hours at regular pay and 4 hours at $12 times 1.5, or an overtime rate of $18. I wanted to fill in cells with the regular hourly rate and overtime and have the spreadsheet calculate how much they would make based on the hours they worked.

Thank You!

 

[Michael_E]

Make Row 4, your overtime row this "=IF(G3>40,(40-G3)*-1,0)" Make whatever your pay row this in the first column "=IF(B3>40,B4*18+(B2-B4)*12,B2*12)", in every subsequent column, "=IF(C3>40,IF(C4>C2,C2*18,C4*18+(C2-C4)*12),C2*12)" This is assuming that the blank space in the top left of your spreadsheet is A1.

 

[jefraz]

Make Row 4, your overtime row this "=IF(G3>40,(40-G3)*-1,0)" Make whatever your pay row this in the first column "=IF(B3>40,B4*18+(B2-B4)*12,B2*12)", in every subsequent column, "=IF(C3>40,IF(C4>C2,C2*18,C4*18+(C2-C4)*12),C2*12)" This is assuming that the blank space in the top left of your spreadsheet is A1.

Thank You Michael. I am almost there! Here is my next question.

	day1	day2	day3	day4	day 5	day 6	day 7		pay rate
emp hrs	8	9	8	10	9	5	0		$12.00
total hrs	8	17	25	35	44	49	49
ot hours	0	0	0	0	4	9	9
emp $	$96	$108	$96	$120	$132	$114	$54

<tbody>
</tbody>

After putting in the formulas this is what was calculated. On day 5 it looks good, 5 reg hours * 12 and 4 OT hours *18, which equals $132. The problem is that the 5 hours on day 6 should all be OT hours. It should be 5*$18 = $90 and then on day 7 there were no hours worked so that should be $0. Again, thank you for your help!!

 

[gaz_chops]

Try

=((MIN(40,B3)*12)-SUM($A$6:A6))+B4*18

 

[gaz_chops]

Ignore the last one, try

=((MIN(40,B3)*12)+(MAX(0,B3-40)*18)-SUM($A$6:A6))

 

[bosco_yip]

Another option :

1] TOTAL hrs B3, copy across :

=SUM(A3,B2)

2] OT hrs B4, copy across :

=MAX(0,B3-40)

3] EMP $ B5, copy across :

=MAX(B2-B4,0)*12+(B4-SUM(A4:A4))*18