IF, OR, AND, Color symbol

NaomiC
NaomiC ✭✭
edited 02/14/23 in Formulas and Functions

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

Best Answer

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Answer ✓

    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"))

Answers

  • Scott Peters
    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
    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
    Scott Peters ✭✭✭✭✭✭
    Answer ✓

    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
    NaomiC ✭✭

    Thank you very much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!