• 410K Members
• 9,810 Online
• 466K Conversations

## excel Formula Help

Occasional Visitor

# excel Formula Help

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.

7 Replies

# Re: excel Formula Help

That could be

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

# Re: excel Formula Help

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

# Re: excel Formula Help

@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

# Re: excel Formula Help

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

# Re: excel Formula Help

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

# Re: excel Formula Help

I’d answer the same way as Sergei

# Re: excel Formula Help

To avoid negative overtime you could wrap it in MAX 0

=MAX( 0, TotalOvertime - MAX(0, 40-TotalRegular). )
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies