# Need help with conditional IF statement

#### smoochie2069

##### New Member
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 4

<tbody>
</tbody>

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
If Your data starts in the range B3-H3 then try this:
=IF(COUNTIF(B3:H3,"Vacation")>0,0,I3-40)

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?

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)

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!

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

Replies
0
Views
902
Replies
5
Views
559
Replies
9
Views
636
Replies
3
Views
638
Replies
1
Views
122

1,203,460
Messages
6,055,557
Members
444,797
Latest member
18ecooley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back