Help with Health Symbol

Beth Fantozzi 1

I have a formula to populate the health status but would like it to return "Green", regardless of whether there's information in the End Date. It seems to me like this should do it. But as you can see in the below screen shot, when the status is "Complete", the Health field is blank.

Here's the text of the formula in case that helps: =IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(ISBLANK([End Date]@row), " ", IF(OR(Status@row = "Complete", [End Date]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [End Date]@row <= TODAY(7), [End Date]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [End Date]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray"))))))

Would someone mind being a second set of eyes here? Thank you!

Eric Law

@Beth Fantozzi 1

With this many, it is easiest for me to just write formulas per status that I want. I will start with the parent one first, as that is the one you want to be evaluated first.

=IF(COUNTIF(CHILDREN(Health@row),"Red")>0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow")>0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green")>0, "Green", [[Children Formula]])))

For the children, here is the formula. You only need to evaluate the status once since if it's complete it is already green.

=IF(OR(Status@row="Complete",[End Date]@row>Today(7)),"Green", IF(ISBLANK([End Date]@row), "", IF([End Date]@row<Today(3), "Red", IF([End Date]@row>=Today(3),"Yellow",""))))

Final formula is =IF(COUNTIF(CHILDREN(Health@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", [End Date]@row > TODAY(7)), "Green", IF(ISBLANK([End Date]@row), "", IF([End Date]@row < TODAY(3), "Red", IF([End Date]@row >= TODAY(3), "Yellow", "")))))))

Amanda H

Try this: =IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(OR(Status@row = "Complete", [End Date]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [End Date]@row <= TODAY(7), [End Date]@row >= TODAY(3)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Cancelled", [End Date]@row <= TODAY(2)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Cancelled"), "Gray", "Green")))))

Remove the check if blank | IF(ISBLANK([End Date]@row), " ", | and include Green as the default value in the final part of the formula |"Green"))))) |

I hope this helps and works to solve your problem. This is my first time answering a question so please let me know if this works. Thank!

Beth Fantozzi 1

Amanda, I appreciate you taking the time to take a look! I tried your solution and it's not quite right. When I used it, I lost the ability to keep my health symbol blank if no end date has been entered. And instead, everything turned Red if there wasn't an end date.

It seems to me that this is the part that's calling for a Green Harvey Ball: IF(OR(Status@row = "Complete", [End Date]@row >= TODAY(7)), "Green"

I'm reading that as if the status isn't Complete or if the end date is 7 days+ into the future, then it would be green. But that's not what I'm seeing so I'm stumped!

Eric Law

@Beth Fantozzi 1 Please use the @username when responding, as it is easier for people to reply to you.

Could you outline what you want it to show in the health column outside of the formula? Some people (myself being this subset) are not good at reading formulas but can help create it. Also, we may be able to assist with anything that could be cleaned up.

Beth Fantozzi 1

@Eric Law Thank you for your comments and the suggestion to use an @ mention! Here's what I'm trying to accomplish with the above formula:

• Green: Status column is "Complete" or the End Date is 7 days out or further
• Yellow: Status column is not "Complete" and the End Date is within the next 7 days
• Red: Status column is not "Complete" and the End is within the next 2 days or is in the past
• Blank: Health column is blank if End Date is blank
• If it's a parent row it shows a red health if any of the children rows under it have a red health, yellow health if any of the children rows show yellow (but not a red), and green if all children are green.

Eric Law

@Beth Fantozzi 1

With this many, it is easiest for me to just write formulas per status that I want. I will start with the parent one first, as that is the one you want to be evaluated first.

=IF(COUNTIF(CHILDREN(Health@row),"Red")>0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow")>0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green")>0, "Green", [[Children Formula]])))

For the children, here is the formula. You only need to evaluate the status once since if it's complete it is already green.

=IF(OR(Status@row="Complete",[End Date]@row>Today(7)),"Green", IF(ISBLANK([End Date]@row), "", IF([End Date]@row<Today(3), "Red", IF([End Date]@row>=Today(3),"Yellow",""))))

Final formula is =IF(COUNTIF(CHILDREN(Health@row), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(Health@row), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(Health@row), "Green") > 0, "Green", IF(OR(Status@row = "Complete", [End Date]@row > TODAY(7)), "Green", IF(ISBLANK([End Date]@row), "", IF([End Date]@row < TODAY(3), "Red", IF([End Date]@row >= TODAY(3), "Yellow", "")))))))

Beth Fantozzi 1

@Eric Law That worked like a charm. Thank you!