Weekly Timesheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2251579%22%20slang%3D%22en-US%22%3EWeekly%20Timesheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2251579%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there.%26nbsp%3B%20I'm%20trying%20to%20build%20a%20weekly%20spreadsheet%20for%20employee%20time.%20See%20attached%20file.%26nbsp%3B%20I'm%20having%20trouble%20with%20the%20formula%20to%20calculate%20an%20overtime%20total%20for%20each%20day%2C%20based%20on%20a%20maximum%2010%20hours%20of%20regular%20time%20a%20week%20and%20a%20maximum%20regular%20time%20of%2040%20hours%20per%20week.%26nbsp%3B%20Does%20anyone%20have%20any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2251579%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-2252052%22%20slang%3D%22en-US%22%3ERe%3A%20Weekly%20Timesheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2252052%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1015937%22%20target%3D%22_blank%22%3E%40Jamie_Wurm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20daily%20overtime%20use%20below%20formula%20in%20cell%20E6%2C%20drag%20it%20down%20and%20copy%20in%20rest%20of%20the%20Days%20columns%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(MAX(0%2C(D6-10))%26gt%3B10%2C10%2CMAX(0%2C(D6-10)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EFor%20week%20use%20this%20formula%20in%20cell%20U6%20and%20drag%20it%20down%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(MAX(0%2C(S6-40))%26gt%3B40%2C40%2CMAX(0%2C(S6-40)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi there.  I'm trying to build a weekly spreadsheet for employee time. See attached file.  I'm having trouble with the formula to calculate an overtime total for each day, based on a maximum 10 hours of regular time a week and a maximum regular time of 40 hours per week.  Does anyone have any suggestions?

 

Thanks

2 Replies

Hi @Jamie_Wurm 

 

For daily overtime use below formula in cell E6, drag it down and copy in rest of the Days columns 

=IF(MAX(0,(D6-10))>10,10,MAX(0,(D6-10)))

For week use this formula in cell U6 and drag it down

=IF(MAX(0,(S6-40))>40,40,MAX(0,(S6-40)))

 

Thanks

Tauqeer

 

@tauqeeracma 

As variant

=MIN(MAX(0,(D6-10)),10)