How to Write IF Statement for a Symbol Column

I have a Symbol column that I'd like to auto-change to Green, Yellow, or Red depending on the date values in my Start, Finish, and Project Status columns.

For instance: If Start is in the past and Finish is in the future and Project Status is not "Complete" or "In Progress", Symbol changes to Yellow.

I really wish automation would include Symbol changes; or am I missing something there?

Best Answer

  • Tim Shaded
    Tim Shaded ✭✭✭✭
    Answer ✓

    Below is an example formula that would accomplish that. A couple assumptions taken are that Progress options are limited to following: Not Started, In Progress, Complete. Additionally, Green status is to indicate "Complete", Yellow status indicates all good progress, and Red status indicates issue (ie. Start Date is in the past but progress shows "Not Started"). The formula can be modified to account for many different variants.

    =IF(Progress@row = "Completed", "Green", IF(AND([Start Date]@row < TODAY(0), [End Date]@row > TODAY(0), Progress@row = "In Progress"), "Yellow", IF(AND([Start Date]@row < TODAY(0), Progress@row = "Not Started"), "Red", "Yellow")))

Answers

  • Tim Shaded
    Tim Shaded ✭✭✭✭
    Answer ✓

    Below is an example formula that would accomplish that. A couple assumptions taken are that Progress options are limited to following: Not Started, In Progress, Complete. Additionally, Green status is to indicate "Complete", Yellow status indicates all good progress, and Red status indicates issue (ie. Start Date is in the past but progress shows "Not Started"). The formula can be modified to account for many different variants.

    =IF(Progress@row = "Completed", "Green", IF(AND([Start Date]@row < TODAY(0), [End Date]@row > TODAY(0), Progress@row = "In Progress"), "Yellow", IF(AND([Start Date]@row < TODAY(0), Progress@row = "Not Started"), "Red", "Yellow")))

  • @Tim Shaded That did the trick, thank you!

  • Tim Shaded
    Tim Shaded ✭✭✭✭

    I'm happy to hear that. We did a very similar setup on our project plans.

  • Corin
    Corin ✭✭✭
    edited 05/11/23

    @Tim Shaded How would I write an if statement for a symbol column based on what I have in a stage column? Ex. IF Stage column says Complete= blue, If On Hold = red, If any of these: Not Started, Pre-Install QC Survey, Pending Install IH, Pending Install BP, Post-Install QC, Follow-up =green, to show in the Status (symbol) column?

  • Corin
    Corin ✭✭✭
    edited 05/11/23

    Thank you for any help you're able to give!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!