RYG Health Status based on "Activity Status" and Sprint End Date

Options

I'm a fairly new Smartsheet User but starting to catch on fast.

Please forgive the noobie question.

I'm trying to set a Red, Yellow, Green - Health Status to an activity but I would like it to be based on the following parameters:

1) Red IF Today(-3)days from the [Sprint End Date] AND [SPRINT ACTIVITY STATUS] = "In Progress" OR "In QA"

Next Requirement for that same cell

2) Yellow IF Today(-6)days from the [Sprint End Date] AND [Sprint Activity Status] = "In Progress" OR "In QA" OR "On Hold"

Thanks in advance for the help.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Internet is back up for a minute!


    Ok. In that case here is what we are working with:

    If the Status is "Complete" then green

    If the Status is not complete and the [Sprint End Date] is in the past then red

    If the Status is "In Progress" or "In QA" and the [Sprint End Date] is in the next three days then red

    If the Status is "In Progress" or "In QA" or "On Hold" and the [Sprint End Date] is in the next 6 days then yellow


    After the formula, I will point out a few potential scenarios that would not output any color based on the above logic. I will also show the difference between trying to follow the same syntax patterns evaluating status and date vs nesting in a specific order.


    Here is the formula I would use for the above criteria:

    =IF(Status@row = "Complete", "Green", IF(OR(AND(OR(Status@row = "In Progress", Status@row = "In QA"), [Sprint End Date]<= TODAY(3)), [Sprint End Date]@row< TODAY()), "Red", IF(AND(OR(Status@row = "In Progress", Status@row = "In QA", Status@row = "On Hold"), [Sprint End Date]@row<= TODAY(6)), "Yellow")))



    Here is a more basic example of the differences between using extra functions for complete and past due vs using nesting logic.

    =IF(AND(Status@row <> "Complete", [Sprint End Date]@row< TODAY()), "Red", IF(Status@row = "Complete", "Green", ...................


    vs leveraging nesting logic

    IF(Status@row = "Complete", "Green", IF([Sprint End Date]@row< TODAY(), "Red", ...................


    You can see that knowing our criteria and leveraging nesting logic makes an impact on both syntax as well as efficiency (and can save some keystrokes too).


    It also looks like if the status is anything other than one of the ones specifically mentioned, it will show as blank (until it becomes past due). And any of the statuses mentioned will be blank if the end date is 7 or more days in the future.

    That's not to say you HAVE to change anything. If it works for you then it works for you, but I just wanted to point out that there may be some unexpected gaps in the logic if we haven't thought about what we want to see for each scenario (or at least the scenarios that we want to see some kind of indicator for).

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What about all the rest of the variable such as On Hold but greater than 6 days in the future or any of the other statuses?

  • JacksonElla
    JacksonElla ✭✭
    edited 08/09/23
    Options

    @Paul Newcome - I'll cross that bridge when I get there :)

    I figured if I could get the baseline of this formula, I could then modify it for other criteria that I need as well.

    Or am I not approaching this correctly?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    In theory, sure. You could do that. But the same syntax for a couple of variables could end up being extremely inefficient with a lot of variables.


    For example, with just the few variables in your post, I would use an AND statement to specify the status/date combinations, but if you want "Green" for anything completed, we would just ignore the date criteria. Or if you wanted "Red" for anything past due regardless of status (with the exception of completed), we could nest that in a specific order to keep from having to use an OR statement to list out every other status.


    If you want, I can go ahead and write the formula out specifically for your original post. It just may not be the most ideal syntax for your overall needs.

  • JacksonElla
    Options

    You're right Paul, Anything Completed should be Green.

    And yes, you're right - anything past due should be Red - regardless of Status.

    I'd really appreciate your help on the formula with the addition of your suggestions for Complete and Past Due.

    Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    So I had a whole piece typed up and ready to go when my internet went out. Unfortunately it's a bit too much to type on a cell phone, so just bear with me until it comes back up and I can get my full response (including formula) back in here.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Internet is back up for a minute!


    Ok. In that case here is what we are working with:

    If the Status is "Complete" then green

    If the Status is not complete and the [Sprint End Date] is in the past then red

    If the Status is "In Progress" or "In QA" and the [Sprint End Date] is in the next three days then red

    If the Status is "In Progress" or "In QA" or "On Hold" and the [Sprint End Date] is in the next 6 days then yellow


    After the formula, I will point out a few potential scenarios that would not output any color based on the above logic. I will also show the difference between trying to follow the same syntax patterns evaluating status and date vs nesting in a specific order.


    Here is the formula I would use for the above criteria:

    =IF(Status@row = "Complete", "Green", IF(OR(AND(OR(Status@row = "In Progress", Status@row = "In QA"), [Sprint End Date]<= TODAY(3)), [Sprint End Date]@row< TODAY()), "Red", IF(AND(OR(Status@row = "In Progress", Status@row = "In QA", Status@row = "On Hold"), [Sprint End Date]@row<= TODAY(6)), "Yellow")))



    Here is a more basic example of the differences between using extra functions for complete and past due vs using nesting logic.

    =IF(AND(Status@row <> "Complete", [Sprint End Date]@row< TODAY()), "Red", IF(Status@row = "Complete", "Green", ...................


    vs leveraging nesting logic

    IF(Status@row = "Complete", "Green", IF([Sprint End Date]@row< TODAY(), "Red", ...................


    You can see that knowing our criteria and leveraging nesting logic makes an impact on both syntax as well as efficiency (and can save some keystrokes too).


    It also looks like if the status is anything other than one of the ones specifically mentioned, it will show as blank (until it becomes past due). And any of the statuses mentioned will be blank if the end date is 7 or more days in the future.

    That's not to say you HAVE to change anything. If it works for you then it works for you, but I just wanted to point out that there may be some unexpected gaps in the logic if we haven't thought about what we want to see for each scenario (or at least the scenarios that we want to see some kind of indicator for).

  • JacksonElla
    Options

    Thank you Paul! I very much appreciate the help.

    Here's to buying you a virtual beverage!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!