No health for parent or milestone tasks
Here's my Health column formula:
=IF([% Complete]@row = 0, "", IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, Finish@row > TODAY(+5)), "Green", IF(AND([% Complete]@row < 100, Finish@row < TODAY(+6), Finish@row > TODAY()), "Yellow", IF(AND([% Complete]@row < 1, Finish@row <= TODAY()), "Red")))))
For the MOST PART it works just fine. However, I need to modify it so that there is no health for the parent or milestone tasks.
Our late task report it is pulling in parent tasks and milestone tasks in error and it skews the report by pulling in tasks that shouldn't be there. (the parent task is not the late task the children are late, and the milestone is simply a marker task and should not have a health rating at all) and before anyone asks. NO I can't change the late report and the team who "owns" the report refuses to do so, so I am thinking about skinning this cat a different way so-to-speak.
Answers
-
Could you use an IF to only evaluate the current formula if the milestone checkbox is not checked and the child count for the row is <0 If either thing is true, do nothing.
-
That makes sense.. I'm just not good with formulas so I'm not sure how to do this. 😳
-
Your Health column formula is good! You can do this!
This formula
=IF(Milestone@row = 1, "",paste your formula here)
will put nothing in the cell if the milestone is ticked
This formula
=IF(COUNT(CHILDREN()) > 0, "", paste your formula here)
will put nothing in the cell if the row has children
And they can be used together like this
=IF(COUNT(CHILDREN()) > 0, "", IF(Milestone@row = 1, "",paste your formula here))
So the final thing is
=IF(COUNT(CHILDREN()) > 0, "", IF(Milestone@row = 1, "",IF([% Complete]@row = 0, "", IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, Finish@row > TODAY(+5)), "Green", IF(AND([% Complete]@row < 100, Finish@row < TODAY(+6), Finish@row > TODAY()), "Yellow", IF(AND([% Complete]@row < 1, Finish@row <= TODAY()), "Red")))))))
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives