Automatic RYGB Lights
Hi All,
Currently I'm trying to make my RYGB column give the right color light when it meets certain criteria. This must be based on a Start Date column, End Date column and a status column, but I can't seem to figure out a formula that works.
So the way it should work is like below:
Is there anyone who might know a solution? 😃
Thanks in advance!
Best Answer
-
@Jeffrey Wagemans I created the formula based on your table, although it has gaps and you will begin to notice them when users begin to use the fields. I found one but have not changed the formula since you provided the needed information.
=IF(Status@row = "not started", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "Blue", IF([Start Date]@row > TODAY(), "Blue", "Red")), IF(Status@row = "in progress", IF([End Date]@row < TODAY(), "Red", "Yellow"), IF(AND(Status@row = "Completed", [End Date]@row < TODAY()), "Green", IF(Status@row = "Canceled", "Blue", "Blue"))))
I created a formula similar to this on my Project Plans. I opted by not caring what the user had on the Status field unless it was something critical like "At Risk" or "Abandoned" or "Completed". I simply used the Date fields to guide my Status. You can kind of gauge what status it should be based on the dates.
This is a snippet of the formula but I think it conveys the message. Don't mind the parenthesis as I simply cut this from my formula without checking if the parentheses have pairs.
IF(AND(NOT(ISBLANK([Start Date]#)), NOT(ISBLANK([End Date]#))), IF(AND([Start Date]# <= TODAY(), [End Date]# >= TODAY()), "In Progress", IF(AND([Start Date]# <= TODAY(), [End Date]# < TODAY()), "Completed", "Planned Not Started")), "Unplanned"))
Answers
-
@Jeffrey Wagemans I can help you make the formula, it would be quite a few nested IF statements. Normally I would take the column that has the most options per group as the initial criteria since it will result in fewer IF statements.
-
@Jeffrey Wagemans I created the formula based on your table, although it has gaps and you will begin to notice them when users begin to use the fields. I found one but have not changed the formula since you provided the needed information.
=IF(Status@row = "not started", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "Blue", IF([Start Date]@row > TODAY(), "Blue", "Red")), IF(Status@row = "in progress", IF([End Date]@row < TODAY(), "Red", "Yellow"), IF(AND(Status@row = "Completed", [End Date]@row < TODAY()), "Green", IF(Status@row = "Canceled", "Blue", "Blue"))))
I created a formula similar to this on my Project Plans. I opted by not caring what the user had on the Status field unless it was something critical like "At Risk" or "Abandoned" or "Completed". I simply used the Date fields to guide my Status. You can kind of gauge what status it should be based on the dates.
This is a snippet of the formula but I think it conveys the message. Don't mind the parenthesis as I simply cut this from my formula without checking if the parentheses have pairs.
IF(AND(NOT(ISBLANK([Start Date]#)), NOT(ISBLANK([End Date]#))), IF(AND([Start Date]# <= TODAY(), [End Date]# >= TODAY()), "In Progress", IF(AND([Start Date]# <= TODAY(), [End Date]# < TODAY()), "Completed", "Planned Not Started")), "Unplanned"))
-
Hi Emilio!
Thank you so much, it worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!