overtime calculation

column AK is where I need the formula. the first 2 are salary, so no OT.

Thanks

For first week you can calculate overtime hours by this formula

=IF(SUM(B13:F13)-($B$6*5)<0,0,SUM(B13:F13)-($B$6*5))

You can calculate overtime for each week in multiple columns by putting the same formula just change the range of SUM(Range) in formula


Hope it will solve your issue

Thank you, that gets me part of the way. I need to also account for the first 40 hours, so a formula to determine there were 40 hours in the range, and multiply it by the base rate.

You mentioned in your worksheet that Hours per work day are 10. So if you have 5 working days week that you have 50 hours for your week.

Please make it clear that you have 40 working hours per week or 50 working hours per week?

The work week will vary per job per week. The formula needs to calculate all hours 40 or less at the base rate and all hours over 40 at the OT rate for each week (color coded). I am listing 5 working days for the week, but may expand to 6.

Thank you

Put this formula in cell AK13 to calculate basic pay for 1st week

=IF(SUM(B13:F13)<=40,SUM(B13:F13)*VLOOKUP(Sheet1!A13,RATES!$A$1:$B$45,2,FALSE),40*VLOOKUP(Sheet1!A13,RATES!$A$1:$B$45,2,FALSE))

and this formula in cell AM13 to calculate OT for first week

=IF(SUM(B13:F13)>40,(SUM(B13:F13)-40)*VLOOKUP(A13,RATES!$A$1:$E$45,4,FALSE),0)


Hope it will help you

Please check the attachment