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!

[TABLE="width: 1012"]
<tbody>[TR]
[TD][/TD]
[TD]Sun
[/TD]
[TD]Mon
[/TD]
[TD]Tue
[/TD]
[TD]Wed
[/TD]
[TD]Thu
[/TD]
[TD]Fri
[/TD]
[TD]Sat
[/TD]
[TD]Regular Time
[/TD]
[TD]OT
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee
[/TD]
[TD]14-Sep-14
[/TD]
[TD]15-Sep-14
[/TD]
[TD]16-Sep-14
[/TD]
[TD]17-Sep-14
[/TD]
[TD]18-Sep-14
[/TD]
[TD]19-Sep-14
[/TD]
[TD]20-Sep-14
[/TD]
[TD]Hours
[/TD]
[TD]Hours
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 935"]
<tbody>[TR]
[TD]OPEN-Supervisor
[/TD]
[TD]Shift B
[/TD]
[TD]Vacation
[/TD]
[TD]OFF
[/TD]
[TD]Shift B
[/TD]
[TD]Shift B
[/TD]
[TD]OFF
[/TD]
[TD]OFF
[/TD]
[TD="align: right"]44.0
[/TD]
[TD="align: right"] 4.0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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