Health Formula

Options
Amy F
Amy F ✭✭✭
edited 10/12/22 in Formulas and Functions

I need to build a health formula that is based on the "Target End Date" and "Status" columns.

If the status is "Not Started" or "In Progress" and the target end date is >=today +1 then "yellow", but if it is MORE than today +3 then "red" otherwise leave as "green" Any thoughts? I'm sure and IF(AND combo is needed, just not sure how to put it all together.

ADDED: I also need the items completed to retain the RYG rather than all being green when marked as complete.

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @Amy F

    Hi Amy,

    try this one:

    =IF(OR(Status@row = "In Progress", Status@row = "Not Started"), IF([Target End Date]@row <= TODAY(), "green", IF([Target End Date]@row <= TODAY() + 3, "yellow", "red")), "green")


    Let me know if that one works for you.

    Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Amy F
    Amy F ✭✭✭
    Options

    @Ryan Sides Thank you! With a little tweaking, that got me pretty close. I can't seem to make it pick up the red for anything overdue by 3+ days:

    =IF(OR(Status@row = "In Progress", Status@row = "Not Started"), IF([Target End Date]@row >= TODAY(), "Green", IF([Target End Date]@row <= TODAY() + 1, "Yellow", IF([Target End Date]@row <= TODAY() + 3, "Red"))), "Green")

    I also need the items completed to retain the health rather than all being green when marked as complete.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    Your greater than and less than signs don't match the ones I sent previously. Can you check them?

    It will not be able to maintain the health because the formula relies on the status. It needs to know what to do when marked as complete. It can't look at itself for the answer. Does that make sense?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Amy F
    Amy F ✭✭✭
    edited 10/13/22
    Options

    @Ryan Sides Thank you for replying. It was a tremendous help! I finally got it to work with a little more manipulation.


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


    Is there a way to retain the RYG even when marked complete?



  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Amy F

    Since you're using the TODAY function, this will update the formula every day (when you open the sheet). This means that if you were to use the formula for the "Complete" row, they would all eventually turn Red since the End Date would be in the past, eventually.

    What you could do is set up a new Date column to record exactly when the Status changes to "Complete" (see: Set the current date with the Record a Date action). Then you could use this date as the comparison in your IF statement, when the Status is complete.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!