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
-
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
-
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!
-
I'm happy to hear that. We did a very similar setup on our project plans.
-
@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?
-
Thank you for any help you're able to give!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!