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.

Find more posts tagged with

Accepted answers

All comments

KPH

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.

CrystalJ_Medtronic

https://community.smartsheet.com/discussion/comment/407415#Comment_407415

That makes sense.. I'm just not good with formulas so I'm not sure how to do this. 😳

KPH

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")))))))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.