Formula to auto populate Health based on End Date & Status columns

I can get the formula to work in pieces, but not as a whole... 


My formula:  =IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", "", IF(Status@row = "In Progress", "Green", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF([End Date]@row < TODAY(7), "Yellow", IF([End Date]@row < TODAY(), "Red")))))))


What I want:

If Status is Complete - Health is Green

If Status has Not Started - Health is blank

If Status is On Hold - Health is Blue 

If Status is At Risk - Health is Yellow

If Status is Delayed - Health is Red 

If Status is In Progress (and over 7 days from End Date) - Health is Green

If Status is In Progress and within 7 days of End Date - Health is Yellow

If Status is In Progress and End Date is today or has passed - Health is Red


Thank you in advance!

Best Answer

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Here's another approach using AND() and OR() functions to combine the multiple criteria for green, yellow, and red symbols. (Documentation for Smartsheet functions can be found at https://help.smartsheet.com/functions .)

    Below are the conditions written in expression form...

    BLANK when Status is Not Started

    Status@row = "Not Started"

    BLUE when Status is On Hold

    Status@row = "On Hold"

    GREEN when Status is Complete OR Status is In Progress (and over 7 days from End Date)

    OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7)))

    YELLOW when Status is At Risk OR Status is In Progress and within 7 days of End Date

    OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()) ))

    RED when Status is Delayed OR Status is In Progress and End Date is today or has passed

    OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY())))

    ... insert the conditions into an IF() function.

    The formula...

    =IF(Status@row = "Not Started", "", IF(Status@row = "On Hold", "Blue", IF(OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7))), "Green", IF(OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()))), "Yellow", IF(OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY()))), "Red", "")))))

    The result...

    Cheers!

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭

    From what I can see your issue is that the status never turns Red. That's because you need to switch this:

    IF([End Date]@row < TODAY(), "Red"

    To be in front of this:

    IF([End Date]@row < TODAY(7), "Yellow"

    Does that solve your issue?

  • Michael Culley
    Michael Culley ✭✭✭✭✭

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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓

    Here's another approach using AND() and OR() functions to combine the multiple criteria for green, yellow, and red symbols. (Documentation for Smartsheet functions can be found at https://help.smartsheet.com/functions .)

    Below are the conditions written in expression form...

    BLANK when Status is Not Started

    Status@row = "Not Started"

    BLUE when Status is On Hold

    Status@row = "On Hold"

    GREEN when Status is Complete OR Status is In Progress (and over 7 days from End Date)

    OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7)))

    YELLOW when Status is At Risk OR Status is In Progress and within 7 days of End Date

    OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()) ))

    RED when Status is Delayed OR Status is In Progress and End Date is today or has passed

    OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY())))

    ... insert the conditions into an IF() function.

    The formula...

    =IF(Status@row = "Not Started", "", IF(Status@row = "On Hold", "Blue", IF(OR((Status@row = "Complete"), AND(Status@row = "In Progress", [End Date]@row > TODAY(7))), "Green", IF(OR((Status@row = "At Risk"), AND(Status@row = "In Progress", NOT([End Date]@row <= TODAY()))), "Yellow", IF(OR((Status@row = "Delayed"), AND(Status@row = "In Progress", ([End Date]@row <= TODAY()))), "Red", "")))))

    The result...

    Cheers!

  • Thank you everyone for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!