Forum Discussion
Solved
Calculate maximum working hours of 8 and allocate anything more than 8 to overtime
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
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.
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
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.