Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
How to automate RYG balls using two different column values.
I am attempting to automate the RYG balls using two different columns, the End Date and % Complete columns. The logic is as follows:
1. If the end date is greater than today, the RYG ball = Green IF([End date]2 > TODAY(), "Green"
2. If the % complete is greater than .99, the RYG ball = Green IF([% Complete]2 > .99, "Green"
3. if the % complete is empty/blank, the RYG ball = Yellow IF([% Complete]2 = "Empty", "Yellow"
4. Anything else is red
I have tried the following formulas to no avail. Any suggestions?
=IF(OR([End Date]400 > TODAY(), [% Complete]400 > 0.99, "Green")), IF([End Date]400 = Blank, "Yellow", "Red")
=IF(OR(End Date > TODAY(), [% Complete]2 = "Full"), "Green", IF(ISBLANK([End Date]2 , "Yellow", "Red"))
Comments
-
Hi Jawanza,
How about this?
If your task is done or not due yet, then Green:
IF(OR($[% Complete]2 = 1,$[End Date]2 > TODAY()), "Green",If your task is not done and the due date has past then Red, otherwise Yellow:
IF(AND($[% Complete]2 < 1,$[End Date]2 <= TODAY()), "Red", "Yellow"))All together:
=IF(OR($[% Complete]2 = 1,$[End Date]2 > TODAY()), "Green",
IF(AND($[% Complete]2 < 1,$[End Date]2 <= TODAY()), "Red", "Yellow"))I strongly suggest a text editor like Notepad++. Use Visual Basic (VBA) as the language.
Good Luck! (:
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives