How to Write IF Statement for a Symbol Column

Tara Daniel

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?

Tim Shaded

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

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

Tara Daniel

@Tim Shaded That did the trick, thank you!

Tim Shaded

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

Corin

@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

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