Harvey Ball Question

Beth Fantozzi 1

I'm finalizing some formulas in a project worksheet and am so close! I need to edit the formula so that only dates that are in the past will turn the health field red. When I make the change in my formula from TODAY(1) to TODAY(-1), I lose my health symbol entirely. What am I doing wrong?

Here's what I want to accomplish:

• 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 4 days
• Red: Status column is not "Complete" and the End 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

In addition to figuring out the red symbol for dates in the past, I'd like to exclude "On Hold" and "Cancelled" status selections. If status is On Hold or Cancelled, I'd like to clear the health symbol.

Here's a screen shot

And here's my formula that I can't get to work for red:

=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(4)), "Green", IF(ISBLANK([End Date]@row), "", IF([End Date]@row < TODAY(1), "Red", IF([End Date]@row >= TODAY(3), "Yellow", "")))))))

Thanks in advance for any help you might be able to provide.

SoS | Dan Palenchar

Hello @Beth Fantozzi 1

I'm not entirely sure what you're desired outputs are with respect to when End Date is Today or in the past, but I think this will get you part of the way at least for your child rows:

=IF(AND(ISDATE([End Date]@row), Status@row <> "On Hold", Status@row <> "Cancelled"), IF(Status@row = "Complete", "Green", IF(NETDAYS(TODAY(), [End Date]@row) < 4, "Red", IF(NETDAYS(TODAY(), [End Date]@row) < 8, "Yellow", "Green"))))

It outputs as shown below. I grouped in colors based on common factors (Status, End Dates, Etc.). Note that in each block the End Dates repeat exactly if they are populated.

For the parents:

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green"))

You can put these formulas together and have this as the overall Health formula:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(AND(ISDATE([End Date]@row), Status@row <> "On Hold", Status@row <> "Cancelled"), IF(Status@row = "Complete", "Green", IF(NETDAYS(TODAY(), [End Date]@row) < 4, "Red", IF(NETDAYS(TODAY(), [End Date]@row) < 8, "Yellow", "Green")))))

I ended up making another Sheet for this and it looks like this.

I have published this Sheet so you can view it. Hope his helps!

_____________________________________________________________________________________________

👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

Beth Fantozzi 1

@Dan Palenchar Thank you so much for taking the time to look at this. I just looked at my post and it looks like some of my notes pulled over from my copy/paste making it very confusing! I'm so sorry and appreciate you wading in even though when I looked at this again, it made no sense at all!

What I'm trying to do is to turn the Harvey Ball red if the date is in the past. So far, when I use my formula, what I get is to make the Harvey Ball null when the date is in the past.

I appreciate your formula but it looks like I've lost the ability to have my ball turn green when complete is selected. I hate to have you go through all this again :) Would you mind taking a look at the formula I've posted above and share an idea of whey my Harvey Ball disappears when the date is in the past?

Bonus points if the status clears when "On Hold" or "Cancelled" is selected. That's not hugely important to me at the moment.