Automate RYG Balls based on Status and then date
Does anyone have a formula that looks at a line status (Not Started, In Progress, Complete) and then determines whether it is after the due date (red), <7 days (yellow) , or >7 days (green)?
Complete would need to turn green regardless of the date while not started and in progress would look at the date parameters.
Thanks in advanced as I am new to Smartsheet and writing these formulas!
Comments
-
Hi apmarcotte,
you can use the following formula that should give you the result you need. For your sheet you need to rename the coloums ([Line Status], [Due Date]) in the formula:
=IF([Line Status]1 = "Complete"; "green"; IF([Due Date]1 - TODAY() < 0; "red"; IF([Due Date]1 - TODAY() < 7; "yellow"; "green")))
Rows with empty dates will be shown as yellow, if you want them to be red, you should use:
=IF([Line Status]1 = "Complete"; "green"; IF(ISBLANK([Due Date]1); "red"; IF([Due Date]1 - TODAY() < 0; "red"; IF([Due Date]1 - TODAY() < 7; "yellow"; "green"))))
Greetings
PS: Maybe you have to exchange all semicolons ; with commas , ... Since I use german language settings, this may not work for you.
-
Thanks Dominik,
This worked great.
-
@Dominik Weller Thank You!! I was struggling to write this formula, so glad I came across this thread.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!