What formula should I use to create a basic health assessment?

DiDL

I am trying to create a health risk assessment for this particular template in Smartsheet. For some reason I am getting the unparseable error code. I want my team to be able to look at this form and see colors that represent where we are at and what is at risk for being "overdue" based on the end date. Green is good, yellow is approaching, red is overdue, ect.

KPH

You can use a nested IF formula to return a color based on the values in other columns. The syntax is as follows where the parts in bold will need to be replaced with your logic:

=IF(logic that when true means good, "Green",IF(logic that when true means overdue, "Red","Yellow")

In this case yellow appears for anything that is not red or green. You could specify logic for yellow as well and have everything else not return a color.

The logic might be something like this:

=IF([Percent Complete]@row = 1, "Green", IF([End Date]@row <= TODAY(), "Red", "Yellow"))

This logic can really be anything you want it to be based on how you define "good" or "overdue".

You will also need the column that the formula is in (the Health column) to be a symbol column.

RDRGSJ00

Hello DiDL,

KPH has the right idea and provided a good solution. If you want to have an actual date range you could try something like this:

=IF([End Date]@row < TODAY(), "Overdue", IF([End Date]@row <= TODAY() + 10, "Approaching", IF([End Date]@row > TODAY() + 30, "Good")))

This allows you to pick specific date ranges, just change the "30" and the "10" to any days out you want. Combined with conditional formatting, you can achieve something like this:

I have the above formula in the [Health] column.