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

Madison Amundson · 10/19/22

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!

Toufong Vang · 10/20/22

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!

Michael Culley · 10/20/22

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 · 10/20/22

=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 · 10/20/22