What's wrong with my formula for setting the Health of a task?
Hello!
I'm trying to write a formula that will update the Health symbol of a task based on some of the other columns in my Smartsheet. I think I'm close, but I don't quite have it.
I want to set the Health symbol to "Yellow" if the date is within 7 days prior to the Target Completion Date OR if my % Complete column is less than the % in the YTD Time Elapsed column by 2% or less. I was able to come up with working formulas for each of these things separately, but I'm having trouble putting them together into one formula. These are the 2 pieces of formula I came up with:
=IF([Target Completion Date]@row > TODAY() - 7, "Yellow")
=IF(AND([% Complete]@row > ([YTD Time Elapsed]@row - 0.02), [% Complete]@row < [YTD Time Elapsed]@row), "Yellow")
The combined formula I came up with is below. It returns the Yellow symbol correctly for the % Complete part of the formula but not the date part of the formula.
=IF(AND([% Complete]@row > ([YTD Time Elapsed]@row - 0.02), [% Complete]@row < [YTD Time Elapsed]@row), IF(OR([Target Completion Date]@row > TODAY() - 7), "Yellow"))
Note that I plan on putting this into a complete formula where the Health symbol is
- Gray if the Status column is Not Started
- =IF((Status@row = "Not Started"), "Gray")
- Red if the Target Completion Date has passed OR % Complete is more than 2% less than the % in the YTD Time Elapsed column
- =IF(OR([Target Completion Date]@row < TODAY(), [% Complete]@row < ([YTD Time Elapsed]@row - 0.02)), "Red")
- Green if none of the situations for Gray, Red, or Yellow are met (on or after the Target Start Date but at least 7 days - or 5 workdays - prior to the Target Completion Date AND % Complete equals or is greater than the % in the YTD Time Elapsed column)
Sorry for the long post, but I'd appreciate any help anyone can provide.
Thanks!
Answers
-
=if(status@row = "Not Started", "Gray", if(or([Target Completion Date]@row < Today(), [% Complete]@row < (YTD Time Elapsed]@row - 0.02)), "Red", if(or(and([% Complete]@row > [YTD Time Elapsed]@row - 0.02, [% Complete]@row < [YTD Time Elapsed]@row), [Target Completion Date]@row > TODAY() - 7), "Yellow", "Green")))
I didn't test it, but it should be close. The main problem with your "Yellow" formula is that you were saying "test if A and B are both true. Then if A and B are both true, test if C is true. Then if C is true, print "Yellow". So if not C, then not "yellow". When what you needed to do was "test if A and B are both true AND test if C is true. IF A and B are both true OR if C is true, then print "Yellow".
As an aside, it seems like you are comparing percentages and days. If this is the case, there is a good chance that you are not accounting for all permutations and you will get rouge results that will require a more complex formula to account for them all. If this is the case, you might be better served by created a matrix and using index and match to get the status.
-
Thanks Daric,
That didn't quite work - anything where % Complete was higher than YTD Time Elapsed was also returning Yellow. But, I managed to work around it by putting Green before Yellow in the formula.
I appreciate your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!