RYG Automation
Would like to automate the RYGB based on % Complete
Blue = 100% complete
Green = <100%
Yellow = 0% past Start Date OR <100% past Finish Date
Red = <100% 5 days past Finish Date
Already have a symbols column "RYG"
Already have "% Complete" column
Best Answer
-
Hi Thai! Try this, assuming your finish date column is called [Finish Date]:
=if([% complete]@row = 1,"Blue",if(AND([% complete]@row < 1, TODAY()>([finish date]@row + 5)),"Red",if([% complete]@row = 0,"Yellow",if(AND([% complete]@row < 1, TODAY()>([finish date]@row)),"Yellow"))))
To translate:
If % complete is 100%, show blue.
If % complete is less than 100% and the finish date is more than 5 days in the past, show red.
If % complete is equal to 0, show yellow.
If % complete is less than 100% and the finish date is in the past, show yellow.
Let me know if it works!
Best,
Heather
Answers
-
Hi Thai! Try this, assuming your finish date column is called [Finish Date]:
=if([% complete]@row = 1,"Blue",if(AND([% complete]@row < 1, TODAY()>([finish date]@row + 5)),"Red",if([% complete]@row = 0,"Yellow",if(AND([% complete]@row < 1, TODAY()>([finish date]@row)),"Yellow"))))
To translate:
If % complete is 100%, show blue.
If % complete is less than 100% and the finish date is more than 5 days in the past, show red.
If % complete is equal to 0, show yellow.
If % complete is less than 100% and the finish date is in the past, show yellow.
Let me know if it works!
Best,
Heather
-
Hi @Thai La
Let's go with this:
=IF([Finish Date]@row>TODAY(), IF( [% Complete]@row =1, "Blue", IF(AND([Start Date]@row<TODAY(), [% Complete]@row =0), "Yellow", "Green")), IF([Finish Date]@row<TODAY(+5), "Red", "Yellow"))
Hope it helped!
-
Hi @Heather Duff your formula works.
I've added Green status to your formula, everything works now. Thanks again!
-
Hi @David Joyeuse your formula almost worked, it was marking 100% complete items Red instead of Blue for some reason though
-
Ah, perfect! Glad it worked.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!