Help with a status health formula please

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.

Tags:

Answers

  • Carson Penticuff
    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
    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
    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
    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. 😁

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!