Help with a status health formula please

Nicole Ross

Hi All, we have developed a fairly basic formula to determine the health of tasks:

=IF(Status@row = "Complete", "Green", IF(Status@row = "Canceled", "Green", IF(Status@row = "In Progress", "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red", IF(AND(Status@row = "On Hold", [End Date]@row <= TODAY()), "Red")))))

I would really appreciate assistance with making it smarter based on these scenarios:

• If Status@row = “In Progress” and [End Date]@row < TODAY (end date in the past), show red
• If Status@row = “Not Started” and [End Date]@row < TODAY (end date in the past), show red
• If Status@row = “Not Started” and [Start Date]@row < TODAY (start date in the past), show yellow
• If Status@row = “In Progress” and [Start Date]@row >= TODAY (start date is today or in the future), show green (currently showing yellow)

Thank you.

Carson Penticuff

This should function correctly; I have it working in a test sheet. However, there are a couple of disclaimers:

First, this could definitely be made more concise. Because you have some somewhat complex scenarios, I opted for human-readability at the expense of having a longer formula. Some conditions could definitely be combined to optimize this, but it would be slightly more difficult to follow the logic. (at least in my opinion)

Second, I had to make a few assumptions. For example, you do not specify the desired output if Status@row is "On Hold" and [End Date]@row is in the future. I assumed green in this scenario, but I could definitely see the logic in desiring a "yellow" result.

Give it a try.... it can always be tweaked if needed.

Danielle Arteaga

Not sure if you can see @Carson Penticuff 's formula and I can't for some reason, but - just in case - here's one that seems to work:

=IF(OR(Status@row = "Complete", Status@row = "Canceled"), "Green", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row >= TODAY()), "Green", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row >= TODAY()), "Green", "Yellow")))))

As Carson noted, not clear what you want for "On Hold" projects, but I made them yellow (they get caught by the value_if_false portion of the formula).

You could probably condense the "In Progress" and "Not Started" portions of this formula with OR statements, but I agree that human readability may be better here - especially if you need to later edit this.

Carson Penticuff

@Danielle Arteaga Maybe I missed a copy/paste somehow? I appear to have completely left out the formula... that is somewhat embarrassing. Anyway, here is what I meant to post:

=IF(OR(Status@row = "Complete", Status@row = "Canceled", AND(Status@row = "In Progress", [Start Date]@row >= TODAY()), AND(Status@row = "Not Started", [Start Date]@row >= TODAY()), AND(Status@row = "On Hold", [End Date]@row > TODAY())), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), [End Date]@row >= TODAY()), "Yellow", IF(OR(AND(Status@row = "On Hold", [End Date]@row <= TODAY()), AND(Status@row = "In Progress", [End Date]@row < TODAY()), AND(Status@row = "Not Started", [End Date]@row < TODAY())), "Red", "")))

Danielle Arteaga

No worries! I thought it was my browser just not rendering what you added.

Now @Nicole Ross has multiple ways to get where she's going. Can never hurt. 😁

Nicole Ross

Thank you so very much Danielle and Carson. Your assumptions, comments and formulas are enormously helpful!