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

Madison Amundson

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

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

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

=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

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!

Madison Amundson

Thank you everyone for your help!