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

  • KPH
    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.



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

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