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 use end date and percentage complete to automate RYG status
Hi everyone,
Maybe my brain is struggling with IF and AND statements, but this is defeating me.
I am trying to use the end date of a task compared to today's date and the percentage complete (great than or equal to 75%) to drive my status column of red, yellow, green balls.
I think I need to find a way to combine the following forumlas:
Task is at or past the due date and the percentage complete is less than 100% = RED
=IF(TODAY() - [End Date]3 <= 0, "Red")
=IF([% Complete]# < 1 , “Red”)
Task is within 5 days of the due date and the percentage complete is 75% or greater = YELLOW
=IF(TODAY() - [End Date]# >= -5 , “Yellow”)
=IF([% Complete]# >= .75 , “Yellow”)
Task is at or past the due date and the percentage complete is 100% = GREEN
=IF(TODAY() - [End Date]# >= 0 , “Green”)
=IF([% Complete]# = 1 , “Green”)
Any suggestions would be great!
Thanks,
-matt
Comments
-
Hello Matt,
You might try using this syntax instead:
=IF(AND(TODAY(-5) >= Finish1, [% Complete]1 >= 0.75), "Yellow")
You'll want to adapt this for your different symbols, and nest it in one formula:
=IF(AND(...), IF(AND(...), IF(AND(...))))
More on formulas can be found in our Help Center: https://help.smartsheet.com/articles/775363-using-formulas
-
I'm struggling with this as well.
I tried using the formula Shaine suggested and I can get one to work, but as soon as I add additional IF(AND) statements I get an #UNPARSABLE error.
HELP!!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives