SOLVED

Nested AND OR

Copper Contributor

 

 

 

=IF(AND(OR(K2<=TODAY,L2=Y)),(OR(P2<=TODAY,Q2=Y)))

 

 

 

I am trying to get the row to highlight if either (K2 or L2 is True) AND (P2 or Q2 is True)

 

Date in K2 is equal to or less than TODAY OR if L2=Y

AND

Date in P2 is equal to or less than TODAY OR if Q2=Y

 

This is what I am trying to use but it's not working:

=IF(AND(OR(K2<=TODAY,L2=Y)),(OR(P2<=TODAY,Q2=Y)))

 

Help, please

 

4 Replies
best response confirmed by brendababy02 (Copper Contributor)
Solution

@brendababy02 

Try it this way:

 

=AND(OR($K2<=TODAY(),$L2="Y"),OR($P2<=TODAY(),$Q2="Y"))

 

You don't need the IF, the correct syntax for the TODAY function is to add the two brackets, and when you compare to a text you must enclose the text in quotation marks. And finally, you need to fix the column references with a $ sign.

Thank you, I forgot about the brackets and the $. Now it is highlighting the entire row even though nothing is entered. I think this has to do with the < for the date. Any suggestions?

@brendababy02 Add another rule (should be the first one in the list) and mark it to "Stop if true". For instance, if A2 is empty than don't format anything.

 

=ISBLANK($A2)

 

Thank you, that fixed it. I'm still trying to figure out Excel. Now to add my other formulas in other cells and hopefully have them in the right order. Thank you, thank you, thank you.
1 best response

Accepted Solutions
best response confirmed by brendababy02 (Copper Contributor)
Solution

@brendababy02 

Try it this way:

 

=AND(OR($K2<=TODAY(),$L2="Y"),OR($P2<=TODAY(),$Q2="Y"))

 

You don't need the IF, the correct syntax for the TODAY function is to add the two brackets, and when you compare to a text you must enclose the text in quotation marks. And finally, you need to fix the column references with a $ sign.

View solution in original post