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 ✭✭✭✭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([email protected]= "Completed", "Green", IF(AND([Start Date]@row < TODAY(0), [End Date]@row > TODAY(0), Progre[email protected]= "In Progress"), "Yellow", IF(AND([Start Date]@row < TODAY(0),[email protected]= "Not Started"), "Red", "Yellow")))
Answers
-
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([email protected]= "Completed", "Green", IF(AND([Start Date]@row < TODAY(0), [End Date]@row > TODAY(0),[email protected]= "In Progress"), "Yellow", IF(AND([Start Date]@row < TODAY(0),[email protected]= "Not Started"), "Red", "Yellow")))
-
@Tim ShadedThat 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 ShadedHow 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!
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/81163/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":39,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
