SOLVED

Calculate maximum working hours of 8 and allocate anything more than 8 to overtime

%3CLINGO-SUB%20id%3D%22lingo-sub-3103592%22%20slang%3D%22en-US%22%3ECalculate%20maximum%20working%20hours%20of%208%20and%20allocate%20anything%20more%20than%208%20to%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103592%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20I%20am%20struggling%20with%20a%20formula%20to%26nbsp%3Bcalculate%20maximum%20working%20hours%20of%208%20and%20allocate%20anything%20more%20than%208%20to%20overtime.%20Public%20holidays%20should%20be%20excluded%20as%20per%20sample%20spreadsheet%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3103592%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103717%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20maximum%20working%20hours%20of%208%20and%20allocate%20anything%20more%20than%208%20to%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20workbook%20contains%20links%20to%20another%20workbook%2C%20probably%20for%20the%20public%20holidays.%20That%20makes%20it%20impossible%20to%20edit%20the%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103792%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20maximum%20working%20hours%20of%208%20and%20allocate%20anything%20more%20than%208%20to%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103792%22%20slang%3D%22en-US%22%3EOops%2C%20I%20attached%20the%20file%20again%20with%20lists%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3103867%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20maximum%20working%20hours%20of%208%20and%20allocate%20anything%20more%20than%208%20to%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3103867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20H3%3A%3C%2FP%3E%0A%3CP%3E%3DIF(OR(WEEKDAY(A3)%3D7%2CISNUMBER(MATCH(A3%2CLists!%24A%242%3A%24A%241000%2C0)))%2C0%2CMIN(G3%2CTIME(8%2C0%2C0)))%3C%2FP%3E%0A%3CP%3EIn%20I3%3A%3C%2FP%3E%0A%3CP%3E%3DG3-H3%3C%2FP%3E%0A%3CP%3EBut%20if%20you%20want%20to%20exclude%20Sundays%20and%20public%20holidays%20from%20Overtime%3A%3C%2FP%3E%0A%3CP%3E%3DIF(OR(WEEKDAY(A3)%3D7%2CISNUMBER(MATCH(A3%2CLists!%24A%242%3A%24A%241000%2C0)))%2C0%2CG3-H3)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi all, I am struggling with a formula to calculate maximum working hours of 8 and allocate anything more than 8 to overtime. Public holidays should be excluded as per sample spreadsheet attached

4 Replies

@Janedb 

Your workbook contains links to another workbook, probably for the public holidays. That makes it impossible to edit the formulas.

Oops, I attached the file again with lists
best response confirmed by Janedb (Contributor)
Solution

@Janedb 

In H3:

=IF(OR(WEEKDAY(A3)=7,ISNUMBER(MATCH(A3,Lists!$A$2:$A$1000,0))),0,MIN(G3,TIME(8,0,0)))

In I3:

=G3-H3

But if you want to exclude Sundays and public holidays from Overtime:

=IF(OR(WEEKDAY(A3)=7,ISNUMBER(MATCH(A3,Lists!$A$2:$A$1000,0))),0,G3-H3)

Fill down.

@hans Awesome, works 100% for my requirements
=IF(OR(WEEKDAY(A3)=7,ISNUMBER(MATCH(A3,Lists!$A$2:$A$1000,0))),0,G3-H3)