Need help with conditional IF statement

[smoochie2069]

I need a conditional statement for the cell "OT Hours" that will calculate the amount of hours over 40 from the "Regular Time Hours" but only if none of the previous 7 cells contain "Vacation" in which case, it should be zero. By the way, Shifts are 12 hours and Vacation days are 8 hours so in the example below, the appropriate answer for OT Hours would be 0 (zero). Reason is that an employee does not get paid for hours over 40 unless they actually work over 40 hours not counting vacation time. Thanks in advance!

	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Regular Time	OT
Employee	14-Sep-14	15-Sep-14	16-Sep-14	17-Sep-14	18-Sep-14	19-Sep-14	20-Sep-14	Hours	Hours

<tbody>
</tbody>

OPEN-Supervisor

Shift B

Vacation

OFF

Shift B

Shift B

OFF

OFF

44.0

4.0

<tbody>
</tbody>

 

[Vidar]

If Your data starts in the range B3-H3 then try this:
=IF(COUNTIF(B3:H3,"Vacation")>0,0,I3-40)

 

[smoochie2069]

Thank you for this and this works great if there are hours over 40 but I need something that gives the result as zero if the hours are under 40. This formula creates a credit in the overtime hours when there is less than 40 hours in a week. Any ideas?

 

[Vidar]

Thank you for this and this works great if there are hours over 40 but I need something that gives the result as zero if the hours are under 40. This formula creates a credit in the overtime hours when there is less than 40 hours in a week. Any ideas?

Maybe?
=IF(OR(COUNTIF(B3:H3,"Vacation")>0,I3<40),0,I3-40)

 

[smoochie2069]

Maybe?
=IF(OR(COUNTIF(B3:H3,"Vacation")>0,I3<40),0,I3-40)

Unfortunately, that gave a -40 in the hours when over 40. I'm at a loss!

 

[Vidar]

Unfortunately, that gave a -40 in the hours when over 40. I'm at a loss!

The formula assumes that the weekdays resides in column B to H, and the "Regular Time Hours" in column I.
So you might have to adjust the ranges in the formula to fit Your ranges.

Vidar