Hi, I’m fairly new to Excel formulas, so far so good. Until now. I’m stuck on how to word a formula. It’s a working schedule, therefore I’d like the formula to tell me in cell B7 ‘Shift Error’ for example in cells B2 to B6, if there is no one on a 7:00 start, but also in cells C2 to C6 if there is no one finishing at 20:00, to display the same result in B7. Is this possible? Thanks.

By: Alan

Hi there

Try this formula

`=IF(AND(COUNTIF(B2:B6,"07:00:00")>0,COUNTIF(C2:C6,"20:00:00")>0),"","Shift Error")`

This should show a Shift Error if there is no start time of 07:00 AND no end time of 20:00. It uses the 24 hour clock. So your times would have to be in this format 07:00 & 20:00 or whatever the start/end times are.

Let me know how you get on with that. π

Thanks

Thanks! π

Works great!

How about if a start time of 10:30 OR 11:00 was OK. I tried this, as I figured logically this should work, but it doesn’t.

=IF(AND(COUNTIF(OR(AD10:AD15,”11:00:00″)AD10:AD15,”10:30:00″))>0,COUNTIF(AF10:AF15,”17:00:00″)>0),””,”Shift Error”)

Thanks.

Hi

See if this works for you…

`=IF(AND(OR(COUNTIF(AD10:AD15,"10:30:00")>0,COUNTIF(AD10:AD15,"11:00:00")>0),COUNTIF(AF10:AF15,"17:00:00")>0),"","Shift Error")`

Thanks