IF function – Multiple cells – 2 outcomes

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

3 thoughts on “IF function – Multiple cells – 2 outcomes

  1. andrew walker Post author

    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

  2. Alan

    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.

  3. andrew walker Post author

    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

Leave a Reply

Your email address will not be published. Required fields are marked *