Calculating Overtime based on 40 Hr Work Week Over 3 Week Period

I am logging the total hours for each week for three consecutive weeks for my techs and would like to see if any of them had overtime.The regular hours is based on 40 hrs per week and should total 120 hours for the three week period if they worked all day each week. I would like to reflect any overtime if they worked over 120 hours in the "Overtime Hours" in Column "K". I have tried using "SUMIF" but I can't get it to only show what exceeds the regular 120 hours. Please see "K4" on the attached file. Does anyone know how to make this work?

How about:

Please Login or Register  to view this content.

That will subtract 120 from the total worked hours and take the higher of that or zero - eg, if they work 120 hours or less, it will return 0 for their overtime hours.

If you need to do it on a weekly basis, then something like this:

Please Login or Register  to view this content.

(Note that's an array formula, that has to be entered with CTRL+SHIFT+ENTER not just ENTER key).

That will return 2.5 hours, because employee #4 worled 42.5 hours in week 3, and 40 hours-or-less in weeks 1 and 2.

Well, which approach you take will depend on what your contractual obligations re: overtime pay are.

The first recommendation "=MAX(G4-$K$2,0)" works perfect! Thanks so much!!

I need to track hours monthly instead of only three weeks. I need the Weekly totals to Max at 40 and any overtime time to roll over to the "Overtime Hours" column. Can anyone tell me how to fix this for "C7, D7"?

Column C is your input data, the total hours worked per person. I would recommend that you don't change it, and leave it "total hours worked" with overtime hours specifically called out in column D.

Right now you have "Total Regular Hours" in column M and "Total Overtime Hours" in Column N.

M3=SUM(C3,E3,G3,I3)
N3=SUM(D3,F3,H3,J3,L3)

All you have to do to make M3 the "Total regular hours" instead of "total of all hours" is this:

M3=SUM(C3,E3,G3,I3)-N3

Subtract the overtime hours from the total hours.

OK - I see what you are saying and that will work, thanks! :-)

Thanks again for your help with this, much appreciated!