Need assistance with unparseable project health formula using IF/AND statements
Current Formula:
=IF([% Complete]@row = 1, "Gray"), IF(AND(TODAY(5) < [Due Date]@row, [% Complete]@row < 1), "Red"), IF(TODAY(15) < [Due Date]@row, IF([% Complete]@row < 1, "Yellow", "Green"))))
Goal of formula:
- Red = % Complete is less than 100% and < 5 days of Due Date
- Yellow = % Complete is less than 100% and < 15 days of Due Date
- Green = % complete is less than 100% and > 15 days of Due Date
- Gray = % complete is 100%
Best Answer
-
Try this:
=IF([% Complete]@row = 1, “Gray”, IF([Due Date]@row⇐ TODAY(5), “Red”, IF([Due Date]@row⇐ TODAY(15), “Yellow”, “Green”)))
It looks like there has been some kind of formatting change here in the community. You will need to retype the formula in Smartsheet (don't copy/paste) to get the correct quotes and change the arrows to < followed by = for less than or equal to.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
It looks like we just need to clean up some parenthesis. Give this a try:
=IF([% Complete]@row = 1, "Gray", IF(AND(TODAY(5) < [Due Date]@row, [% Complete]@row < 1), "Red", IF(TODAY(15) < [Due Date]@row, IF([% Complete]@row < 1, "Yellow", "Green"))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Have this exactly now, it is indicating “Gray” for status, but all other parameters are being ignored and indicating Red, regardless of Date/%
-
Try this:
=IF([% Complete]@row = 1, “Gray”, IF([Due Date]@row⇐ TODAY(5), “Red”, IF([Due Date]@row⇐ TODAY(15), “Yellow”, “Green”)))
It looks like there has been some kind of formatting change here in the community. You will need to retype the formula in Smartsheet (don't copy/paste) to get the correct quotes and change the arrows to < followed by = for less than or equal to.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This was it! I see now no need to reinsert %Complete reference as all IF statements after that were the false result for %Complete = 1. Thank you so much!
-
This was it! I see now no need to reinsert %Complete reference as all IF statements after that were the false result for %Complete = 1. Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!