add to cell only if over a certain number

[acncguy]

Hello,

I have a spreadsheet for tracking hours worked. Overtime pay is after 40 hours worked for the week (not after 8 hours per day). The spreadsheet records total hours worked each day with the cells below it labeled “Overtime Hours”.

I want the spreadsheet to only record OT hours worked that day after the 40 hours are reached. For example, say someone worked 12 hours Sunday through Friday. In the OT hours cells, Sunday, Monday and Tuesday would have 0 hours OT, and Wednesday would have 8 hours OT, and Thursday and Friday would have 12 hours OT. I will add them up at the end of the cells.

I currently have the following formula, but it will keep a running tally of hours worked over 40 hours, instead of just giving how many hours for that day were OT hours.

=MAX(0,SUM(E11:K11)-40)

Thanks for any help.

- Paul

 

[Joe4]

Try placing this formula in column E, and then copying across all the way to column K:

=MIN(MAX(0,SUM($E11:E11)-40),E11)