Help to add an extra condition in IF formula

[KELLIS]

Hi everyone

I have a formula

Please Login or Register  to view this content.

which works perfectly to highlight if a job was attended to in Standby time however I have added a column which shows cancelled job as "Yes" "No" I now want my formula to look at
If S3 (Cancelled column) = YES then the overall formula will return "No" in the standby column

I tried

Please Login or Register  to view this content.

which works other than on the first two conditions. i am afraid i cannot upload a sheet at present as I'm at work.

Any suggestions

Thanks

Kelly

[benishiryo]

hi kelly. there's quite a number of problems here:
1) i suppose S3 supersedes whatever that is in O3 or P3? that means if S3 is "Yes", it's "No" in the standby column no matter what O3:P3 are. to do that, the logic must be on the left. the IF formula works from left to right

2) "6" with double quotes is a text. if you add +0, it becomes a number. so you can put in 6 or 7 straight away without the double quotes

3) seems like O3 can be 6 or 7. you have used OR inside your formula, so i suppose you know that.

4) "07:00" is a text. it's not a timing. to make it into a timing, you have to do the +0

5) and it seems that N3 lesser than 7 am or greater or equals to 4 pm can also make it "Yes". this can be combined with the OR statement of O3.

use this first to check with your 1st formula that works:
=IF(O3="","",IF(OR(O3=6,O3=7,N3<"07:00"+0,N3>="16:00"+0),"Yes","No"))

then slot in the S3 in somewhere that takes priority. i'm just guessing here:
=IF(S3="yes","no",IF(O3="","",IF(OR(O3=6,O3=7,N3<"07:00"+0,N3>="16:00"+0),"Yes","No")))

[KELLIS]

Hi Benishyro

Thanks for the help.

If I remove the +0 and quote marks from my 6/7 the formula returns the wrong answer, the column is formatted as a number
also if I add +0 to the time 07:00 which is formatted as a time my formula returns incorrectly

I have amended the formula by adding

Please Login or Register  to view this content.

This seems to work perfect. If you have a moment can you explain the difference between using the +0 and why in my formula it does not work.

Thanks