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

[Janedb]

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

[#2]

@Janedb 

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

[#3]

Oops, I attached the file again with lists

[#4 · Accepted 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.

[#5]

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