How to combine these?

bobloblaw
bobloblaw ✭✭
edited 08/12/23 in Formulas and Functions

Need the formula to say:

  • If Completed - Green
  • If Canceled - Gray
  • If In Progress - Yellow
  • If Not Started - Red
  • If On Hold - Yellow
  • If In Progress, Not Started, or On Hold and and today is greater than End@row - Red

Here is what I have.... Works fine. Now need to add in the last bullet and I'm stumped.

=IF(Status@row = "Completed", "Green", IF(Status@row = "Canceled", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Not Started", "Red", IF(Status@row = "On Hold", "Yellow"

Tags:

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Hi bobloblaw,

    I suggest to add End@row checking into your formula with AND function as below:

    =IF(Status@row = "Completed", "Green", IF(Status@row = "Canceled", "Gray", IF(AND(Status@row = "In Progress", End@row>=TODAY()), "Yellow", IF(AND(Status@row = "Not Started", End@row>=TODAY()), "Red", IF(AND(Status@row = "On Hold", End@row>=TODAY()), "Yellow", IF(AND(OR(Status@row = "In Progress", Status@row = "Not Started", Status@row = "On Hold" ), End@row<TODAY()),"Red")))

    Try it!


    Gia Thinh Co. - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!