Conditional Formatting Formula Help

[Lazarus416]

I'm having some trouble puzzling out a Conditional Formatting formula...it uses AND, NOT & OR functions. I want to highlight the cell under the following conditions..(this is not the actual formula I tried)

(B3 NOT "A" OR "B" OR "C") AND (F3 - H1 >= 7)

B3 is text
F3 is a manually input date and H1 is =TODAY

(I could also make B3 = "A" OR "B" OR "C")

Any ideas on how to make it work? Thanks in advance.

 

[jtakw]

Hi,

Try this:

=AND(OR(B3<>"A",B3<>"B",B3<>"C"),F3-H1>=7)

 

[Marcelo Branco]

Maybe...

=AND(B3<>"A",B3<>"B",B3<>"C",F3-H1>=7)

M.

 

[Lazarus416]

Hi,

Try this:

=AND(OR(B3<>"A",B3<>"B",B3<>"C"),F3-H1>=7)

I had to reverse F3 & H1, but other than that, it works great. Thank you very much!

 

[Marcelo Branco]

I had to reverse F3 & H1, but other than that, it works great. Thank you very much!

Are you sure this works?
I'm asking because this condition
OR(B3<>"A",B3<>"B",B3<>"C")
returns TRUE for any value in B3

M.

 

[Lazarus416]

Are you sure this works?
I'm asking because this condition
OR(B3<>"A",B3<>"B",B3<>"C")
returns TRUE for any value in B3

M.

In this case it is fine because there are only 2 or 3 values that I don't want to trigger the highlight. (It is a status update cell.)

 

[Marcelo Branco]

In this case it is fine because there are only 2 or 3 values that I don't want to trigger the highlight. (It is a status update cell.)

What was i trying to say is:
If the formula worked for you then the condition i mentioned above is superfluous, because it's always returns TRUE for any value in B3.
Please, try to be specific about the values in B3 that should trigger the conditional formatting.

M.

 

[jtakw]

Oops, you're right Marcelo.

I was thinking this: =AND(OR(B3="A",B3="B",B3="C"),F3-H1>=7)

But typed what was in post #2...

 

[Lazarus416]

What was i trying to say is:
If the formula worked for you then the condition i mentioned above is superfluous, because it's always returns TRUE for any value in B3.
Please, try to be specific about the values in B3 that should trigger the conditional formatting.

M.

The values are "Working" or "User Disabled". (I took the 3rd one out because I realized I didn't need it.) But when I tested the formula it works just fine. If I have anything other than 2 values above, then the date cell highlights. If the value is Working or Disabled, then the cell does not highlight. So it works as intended.

Why would that formula return TRUE for any value? It should (rightfully) return FALSE for the A & B values...what did I miss?

 

[Marcelo Branco]

The values are "Working" or "User Disabled". (I took the 3rd one out because I realized I didn't need it.) But when I tested the formula it works just fine. If I have anything other than 2 values above, then the date cell highlights. If the value is Working or Disabled, then the cell does not highlight. So it works as intended.

Sorry, I don't think so.
Let me ask again:
1. If B3="A" do you want the conditional formatting to be triggered?
2. If B3="B" do you want the conditional formatting to be triggered?
3. If B3="C" do you want the conditional formatting to be triggered?
4. If, say, B3="X" do you want the conditional formatting to be triggered?

M.