 SOLVED

Contributor

# 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

4 Replies

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

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

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

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

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

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.

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

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