IF, OR, AND, Color symbol

NaomiC

If next status is <> A or B and Status 2 <> not started or blank and today is 60 days from a date, Red

If next Status is <> A or B and Status 2 <> not started or blank and today is 80 days from a date, Yellow

Green

Scott Peters

That is a little trickier, because "not started" is not blank. One way to do it is to do an OR phrase where you list out all of the other drop down choices. Another way is to nest another AND, like this:

=IF(AND(OR([Next TG]@row <> "Implementation/Transition", [Next TG]@row <> "TG2"), and([NCI State]@row <> "Not Started",[NCI State]@row<>""), [Sales Rev Rent Date]@row > TODAY(80)), "Yellow", IF(AND(OR([Next TG]@row <> "Implementation/Transition", [Next TG]@row <> "TG2"), and([NCI State]@row <> "Not Started",[NCI State]@row<>""), [Sales Rev Rent Date]@row > TODAY(60)), "Red", "Green"))

Scott Peters

Hi @NaomiC - Are you asking for help with a formula to make this concept work? There are many possibilities, here's one:

I'll assume "Next Status" and "Status 2" are the names of your columns, and that you also have a column called "Due Date"

Add a helper column called "Status Check". The column formula would be something like:

=if(and(or([Next Status]@row<>"A", [Next Status]@row<>"B"), [Status 2]@row<>"Not Started",[Due Date]@row>today(80)), "Yellow", if(and(or([Next Status]@row<>"A", [Next Status]@row<>"B"), [Status 2]@row<>"Not Started",[Due Date]@row>today(60)), "Red", "Green")

You can then hide that helper column, but use it in conditional formatting to apply the color to any visible cells you like. Hope this helps!

NaomiC

Thank you, this formula is very helpful.

I've added in the actual column names to your great formula!

We are missing one more element, where NCI State is <> not started, we also need to check for <> Blank

=IF(AND(OR([Next TG]@row <> "Implementation/Transition", [Next TG]@row <> "TG2"), [NCI State]@row <> "Not Started", [Sales Rev Rent Date]@row > TODAY(80)), "Yellow", IF(AND(OR([Next TG]@row <> "Implementation/Transition", [Next TG]@row <> "TG2"), [NCI State]@row <> "Not Started", [Sales Rev Rent Date]@row > TODAY(60)), "Red", "Green"))

Scott Peters

That is a little trickier, because "not started" is not blank. One way to do it is to do an OR phrase where you list out all of the other drop down choices. Another way is to nest another AND, like this:

=IF(AND(OR([Next TG]@row <> "Implementation/Transition", [Next TG]@row <> "TG2"), and([NCI State]@row <> "Not Started",[NCI State]@row<>""), [Sales Rev Rent Date]@row > TODAY(80)), "Yellow", IF(AND(OR([Next TG]@row <> "Implementation/Transition", [Next TG]@row <> "TG2"), and([NCI State]@row <> "Not Started",[NCI State]@row<>""), [Sales Rev Rent Date]@row > TODAY(60)), "Red", "Green"))

NaomiC

Thank you very much