excel Formula Help

[dohmeis1]

Hey all. I need help with a simple time sheet. I have the sheet 95% complete. There is 1 formula that I can not make work. I have the total cell for regular hours worked and then I have a total cell for overtime hours worked. However I need the overtime hours worked cell to reflect that the regular hours worked cell is under 40 hours and reduce the overtime hours by that amount. Please help.

[#2]

@dohmeis1 ,

That could be

=TotalOvertime - MAX(0, 40-TotalRegular)

[#3]

If the result of your formula for RegularHours does not exceed 40, the formula for OvertimeHours may be:
=TotalHours-40

[#4]

@Sergei BaklanThat would go wrong if the amount of claimed overtime is less than the gap between regular hours and 40. Eg Reg=35, OT=3. It also highlights that in fact the regular hours also needs to be increased by an amount to fill the gap. So in fact two things are needed:
RealReg[ular hours] = MIN(TotalReg + TotalOT,40)

then Real OT = TotalReg + TotalOT - RealReg

[#5]

@AdamV , it could be, but not necessary. It depends on business logic taken within the organization.

[#6]

I don't think any orgasnisation would pay negative overtime without also crediting those hours against the regular hours. Unions would be very unhappy!

[#7]

I’d answer the same way as Sergei

[#8]

To avoid negative overtime you could wrap it in MAX 0

=MAX( 0, TotalOvertime - MAX(0, 40-TotalRegular). )