Calculating overtime over several jobs

**furnitureguy** · 05-02-2013, 03:16 PM

Hey everyone, I need a little bit of help here. I am using the attached spreadsheet to try and track how many hours my employees spend on each job (job costing if you will). I have the formulas set up in AA-AC to take anything over 40 hours and apply it to overtime for each job (see rows 4-8 for example). Since row 5 totals to over 40 hours, I would want the total hours (AA) to be applied directly to the OT column, because the employee will technically be on overtime at that point.

I really hope I'm not missing something easy here but I've been staring at this for a few hours and just can't seem to crack the enigma. Thanks in advance and I'll answer any questions as quickly as I can.

Thanks!

**popipipo** · 05-02-2013, 03:31 PM

I changed the orange cells.
Is this what you meant

**FDibbins** · 05-02-2013, 03:43 PM

row 5 does not total 40 (30.75), but when I add another 10 hours to it...

AA5 = 40.75
AB5 = 40
AC5 = 0.75

Am I missing something?
(and on a side note, instead of =G5+J5+M5+P5+S5+V5+Y5 in column Z, you could use this...=SUMIF($E$2:$Y$2,"total",E5:Y5)

**furnitureguy** · 05-02-2013, 03:54 PM

Ok I updated the spreadsheet to maybe make this a little more clear. Column D represents a given job that my employee is working on, and in D5-D7 they are working on 3 different jobs during one week. So if he spends 42.75 hours on the first project (Amerra in this example), any other job he works on is going to be purely overtime since he already cleared 40 hours. In this case, AB6 and AB7 would equal to 0 and AC6 and AC7 would be 4 and 8 respectively.

Say AA5 only equaled 32, AB6 would still be 4, but AB7 would be 4, with the remaining 4 hours (anything over 40), going into AC7.

Sorry for the semi-complicated explanation.

**popipipo** · 05-02-2013, 04:06 PM

use this formula in AB5:
=MIN(40;AA5)

**robemichael** · 03-31-2016, 11:10 AM

I know it's been 3 years, but has anyone ever come up with a solution to this? I have a similar issues. I've put some half hearted attempts at doing this, but to no avail. I have been just calculating manually by looking at what jobs they were on AFTER they hit 40. It would be much better if i could automate this and take human error out of the calculations.