Home

excel Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-675993%22%20slang%3D%22en-US%22%3Eexcel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675993%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all.%20I%20need%20help%20with%20a%20simple%20time%20sheet.%20I%20have%20the%20sheet%2095%25%20complete.%20There%20is%201%20formula%20that%20I%20can%20not%20make%20work.%20I%20have%20the%20total%20cell%20for%20regular%20hours%20worked%20and%20then%20I%20have%20a%20total%20cell%20for%20overtime%20hours%20worked.%20However%20I%20need%20the%20overtime%20hours%20worked%20cell%20to%20reflect%20that%20the%20regular%20hours%20worked%20cell%20is%20under%2040%20hours%20and%20reduce%20the%20overtime%20hours%20by%20that%20amount.%20Please%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-675993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676146%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356113%22%20target%3D%22_blank%22%3E%40dohmeis1%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DTotalOvertime%20-%20MAX(0%2C%2040-TotalRegular)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677139%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677139%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20the%20result%20of%20your%20formula%20for%20RegularHours%20does%20not%20exceed%2040%2C%20the%20formula%20for%20OvertimeHours%20may%20be%3A%3CBR%20%2F%3E%3DTotalHours-40%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677254%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677254%22%20slang%3D%22en-US%22%3EHow%20about%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3Dovertime%20-%20max(0%2C%2040-%20regular)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677258%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677258%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EThat%20would%20go%20wrong%20if%20the%20amount%20of%20claimed%20overtime%20is%20less%20than%20the%20gap%20between%20regular%20hours%20and%2040.%20Eg%20Reg%3D35%2C%20OT%3D3.%20It%20also%20highlights%20that%20in%20fact%20the%20regular%20hours%20also%20needs%20to%20be%20increased%20by%20an%20amount%20to%20fill%20the%20gap.%20So%20in%20fact%20two%20things%20are%20needed%3A%3CBR%20%2F%3ERealReg%5Bular%20hours%5D%20%3D%20MIN(TotalReg%20%2B%20TotalOT%2C40)%3C%2FP%3E%3CP%3Ethen%20Real%20OT%20%3D%20TotalReg%20%2B%20TotalOT%20-%20RealReg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677273%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677273%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356439%22%20target%3D%22_blank%22%3E%40AdamV%3C%2FA%3E%26nbsp%3B%2C%20it%20could%20be%2C%20but%20not%20necessary.%20It%20depends%20on%20business%20logic%20taken%20within%20the%20organization.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677275%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677275%22%20slang%3D%22en-US%22%3EI%20don't%20think%20any%20orgasnisation%20would%20pay%20negative%20overtime%20without%20also%20crediting%20those%20hours%20against%20the%20regular%20hours.%20Unions%20would%20be%20very%20unhappy!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677306%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677306%22%20slang%3D%22en-US%22%3EI%E2%80%99d%20answer%20the%20same%20way%20as%20Sergei%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677387%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677387%22%20slang%3D%22en-US%22%3ETo%20avoid%20negative%20overtime%20you%20could%20wrap%20it%20in%20MAX%200%3CBR%20%2F%3E%3CBR%20%2F%3E%3DMAX(%200%2C%20TotalOvertime%20-%20MAX(0%2C%2040-TotalRegular).%20)%3C%2FLINGO-BODY%3E
dohmeis1
Occasional Visitor

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

@dohmeis1 ,

 

That could be

=TotalOvertime - MAX(0, 40-TotalRegular)

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

@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

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

I don't think any orgasnisation would pay negative overtime without also crediting those hours against the regular hours. Unions would be very unhappy!
I’d answer the same way as Sergei
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