Additional Formula Help Needed
Hello Smartsheets Community!
I have another dilemma with one of the formulas some of you help me with. I was asked if there was a way to write the formula in the scheduled health column so that even at 0 % Complete that the schedule health ball changes to yellow when the end date is in range of the part of the formula that turns the ball yellow with out having to put a numeric value in the % Complete column.
For example, if you look at the first screen shot below. You can see that the End Date is scheduled for 10/29/21 and % Complete is at 0% and the ball color is grey. It only turns yellow as soon as you put a numeric value in the % complete column; second screen shot below.
Is this feasible and if so, how do I adjust my current formula:
=IF(OR(AND([% Complete]@row = 0, [End Date]@row > TODAY()), [% Complete]@row = 1), "Gray", IF(AND([% Complete]@row < 1, (TODAY() - [End Date]@row) > 0), "Red", IF(AND((TODAY() - [End Date]@row) > -3, [% Complete]@row < 1), "Yellow", IF([End Date]@row > TODAY(), "Green"))))
Best Answer
-
Yes, that does make sense - I made the same error as the formula and stopped reading after a certain point!
Lets put the RED statement first, then.
=IF(AND([% Complete]@row < 1, (TODAY() - [End Date]@row) > 0), "Red", IF(AND((TODAY() - [End Date]@row) > -3, [% Complete]@row < 1), "Yellow", IF(OR(AND([% Complete]@row = 0, [End Date]@row > TODAY()), [% Complete]@row = 1), "Gray", IF([End Date]@row > TODAY(), "Green"))))
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Your formula is correct, I would just change around the order! IF Statements will stop reading through the formula as soon as one of the statements meets the criteria.
The first statement you have says that IF the % Complete is 0 but the date is in the future, turn gray. Have this be a statement after your yellow statement and then it will read this second, like so:
=IF(AND((TODAY() - [End Date]@row) > -3, [% Complete]@row < 1), "Yellow", IF(OR(AND([% Complete]@row = 0, [End Date]@row > TODAY()), [% Complete]@row = 1), "Gray", IF(AND([% Complete]@row < 1, (TODAY() - [End Date]@row) > 0), "Red", IF([End Date]@row > TODAY(), "Green"))))
Let me know if this resolved it for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so much for the response. I took your formula and added into my project template. It did change the color ball yellow, but it stays yellow for dates that are now in the past when the color ball should be red. See screen shot below.
Gray - if % Complete is 0 or 100
Yellow - if the end date is within 3 days of that date
Red - if the end date has past
Does that help?
-
Yes, that does make sense - I made the same error as the formula and stopped reading after a certain point!
Lets put the RED statement first, then.
=IF(AND([% Complete]@row < 1, (TODAY() - [End Date]@row) > 0), "Red", IF(AND((TODAY() - [End Date]@row) > -3, [% Complete]@row < 1), "Yellow", IF(OR(AND([% Complete]@row = 0, [End Date]@row > TODAY()), [% Complete]@row = 1), "Gray", IF([End Date]@row > TODAY(), "Green"))))
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
That worked! Thank you so much and thank you for the quick response! I hope you have a great day and a wonderful weekend!
Best,
Anthony D'Ambrosio
-
I'm glad to hear it! I hope you have a wonderful weekend as well. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 211 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 140 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!