Health formula help

Amy NK

Hi - I'm sure lots of folks ask for assistance with similar things, so I apologize if this has already been asked/answered; I just haven't found anything to match my particular situation.

I am trying to create an IF statement that will take into account multiple columns and conditions for a grant funding project management sheet.

If the Status is "Not Pursuing" or "Denied," the health symbol should be Gray.

If the Status is "Approved to Pursue" or "Awarded" or "Complete," the health symbol should be Green.

If the Status is "In Progress/Pending" or "Evaluation" and the Task Deadline column is 4 days or less away, I want the health symbol to be Red.

"In Progress or Pending" or "Evaluation" Status with 5 or more days to complete before the Task Deadline should be Yellow.

This is what I've built and it's creating an "UNPARSEABLE" error - your assistance is appreciated in correcting this:

=IF(OR([Status]@row = “Not Pursuing”, [Status]@row=”Denied”), "Gray", IF(OR([Status]@row = “Approved to Pursue”, [Status]@row = "Complete", [Status]@row =”Awarded”), "Green", IF(OR([Task Deadline]@row <= TODAY(4) AND [Status]@row = “Evaluation”, [Task Deadline]@row <= TODAY(4) AND [Status]@row= “In Progress/Pending”), "Red", "Yellow")))

Thanks!!

Paul Newcome

The first thing to try is correcting the quotes. Notice how some are slanted and others are straight up and down? The slanted ones are called "smart quotes" which (ironically enough) Smartsheet does not recognize as valid characters.

Try retyping the formula here in the community, directly in Smartsheet, or in a text editor such as Notepad (not Word).