Looking for a formula that uses status and finish to calculate health.

nlehmann

Hello Smartsheet experts,

I am trying to write a formula that accomplishes the following in a health column with the Red, Yellow, Green, and Blue options...

Status "Complete" = Green

Status "On Hold" = Blue

Finish "Blank"(or "") = "Blank"(or "")

Status "In Progress" AND Finish is greater than Today, Green

Status "In Progress" AND Finish is within 2 days of Today = Yellow

Status "In Progress" AND Today is 3 or more days past Finish = Red

I must be getting caught up in the correct order of how to enter these Nested Ifs. Any help is appreciated!

Kelly Moore

Hey @nlehmann

Try this

=IF(Status@row = "Complete", "Green", IF(Status@row = "On Hold", "Blue", IF(Finish@row = "", "", IF(AND(Finish@row >= TODAY(), Finish@row <= TODAY(2), Status@row = "In Progress"), "Yellow", IF(AND(Status@row = "In Progress", Finish@row <= TODAY(3)), "Red", IF(AND(Status@row = "In Progress", Finish@row > TODAY()), "Green"))))))

Does it work for you?

Kelly