Combining ISBLANK and TODAY formula for RYG Symbol Column

r0030

Hi all,

I'd like to automate the Health column in my Project Plan but am not sure how to go about doing this. The Health column is a symbol column (red, yellow, green). Ideally, I'd like for it to show the red symbol if the end date is 3 days late, yellow if the end date is 2 days late, green if we're on track, and blank if there is no end date in the end date column. The formula also needs to consider if the task has been completed (there is a status column which indicates if the associated task has not been started, is not applicable, is in progress, is complete, on hold, or cancelled). Could anybody help?

Screenshots below for reference:

Mark Safran

Here you go @rmc0030 :

=IF(Status@row = "Complete", "", IF(ISBLANK([End Date]@row), "", IF(TODAY() - [End Date]@row >= 3, "Red", IF(TODAY() - [End Date]@row >= 1, "Yellow", IF(TODAY() - [End Date]@row <= 1, "Green", "")))))

-MCS

Mark Safran

Hi @rmc0030

You'll want to do this with nested IF( ) statements. Try using this formula:

=IF(Status@row = "Complete", "", IF(TODAY() - [End Date]@row >= 3, "Red", IF(TODAY() - [End Date]@row >= 1, "Yellow", IF(TODAY() - [End Date]@row <= 1, "Green", ""))))

This assumes the following:

• If the Status is 'Complete', Health is blank.
• If Status is anything else, and the End Date is 3 or more days in the past, Health is Red
• If Status is anything else, and the End Date is 1 or 2 days in the past, Health is Yellow
• If Status is anything else, and the End Date is today or in the future, Health is Green

If you can elaborate on how you want each of the other status values to be handled we can edit this further. But nested IF( ) statements is the way to go here.

-MCS

r0030

https://community.smartsheet.com/discussion/comment/335454#Comment_335454

Thanks so much, Mark! This is super helpful. With the nested functions, how would I also add a statement to say that if the End Date column does not contain a date/is blank, also leave the Health column blank?

Mark Safran

Here you go @rmc0030 :

=IF(Status@row = "Complete", "", IF(ISBLANK([End Date]@row), "", IF(TODAY() - [End Date]@row >= 3, "Red", IF(TODAY() - [End Date]@row >= 1, "Yellow", IF(TODAY() - [End Date]@row <= 1, "Green", "")))))

-MCS

r0030

Thank you so much, @Mark Safran !