Dynamically update Health column using Symbols based on proximity to Due Date

Bviola

I would like to use the red, green, yellow, and grey symbols in the Health column of a smartsheet and would like it to dynamically update the value of each depending on how far out the due date is.

Basically, if the due date is more than 7 days out, I want it Green, if the due date is less than 7 days out, I want it Yellow, if the due date is past due, I want it to turn Red.

I tried writing this and got an unparseable message:

=IF([Due Date]@row < 8, "Yellow") ELSE IF([Due Date]@row <1 "Red"), ELSE IF([Due Date]@row >7 = "Green", "Gray")

Anyone know how to fix it? Thanks in advance!

UPDATE: I also just tried the following and it also did not work…

=IF([Due Date]@row <1, "Red", IF([Due Date]@row <8 AND >1, "Yellow", IF([Due Date]@row >7, "Green")))

Spoonhead

The formula you need should consider the difference between the current date and the due date. Smartsheet formulas do not use ELSE IF statements, but rather nested IF statements. Also, when using date calculations, we can leverage the TODAY() function to get the current date.

Here’s the corrected formula for your requirements:

=IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY() + 7, "Yellow", "Green"))

This formula works as follows:

1. If the due date is before today, it will display "Red".
2. If the due date is within the next 7 days (including today), it will display "Yellow".
3. If the due date is more than 7 days away, it will display "Green".

If you also want to include a "Gray" status for items that do not fit into any of these categories, you can modify the formula like this:

=IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY() + 7, "Yellow", IF([Due Date]@row > TODAY() + 7, "Green", "Gray")))

This adds a fallback "Gray" status, but based on your requirements, the original three-color formula should suffice.

Make sure the column [Due Date] is correctly formatted as a date column, and you should see the desired colour codes updating dynamically.

Kerry St. Thomas

I am assuming here that column DUE DATE is type Date. With that in place, you can make aa Symbol column with Green/Yellow/Red/Gray as the choices, and use the following formula:

=IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row < TODAY(), "Red", IF(NETDAYS(TODAY(), [Due Date]@row) > 7, "Green", "Yellow")))

The first clause makes a GRAY return only if the Due Date is blank. The reason your formulas didn't work is because you were saying essentially "If July 13 is less than 1, do this" and so on - the math don't math. 😀 You need to do math to find out how far removed from the current date your due date is first, and use THAT as your comparison.

Good luck!

Spoonhead

The formula you need should consider the difference between the current date and the due date. Smartsheet formulas do not use ELSE IF statements, but rather nested IF statements. Also, when using date calculations, we can leverage the TODAY() function to get the current date.

Here’s the corrected formula for your requirements:

=IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY() + 7, "Yellow", "Green"))

This formula works as follows:

1. If the due date is before today, it will display "Red".
2. If the due date is within the next 7 days (including today), it will display "Yellow".
3. If the due date is more than 7 days away, it will display "Green".

If you also want to include a "Gray" status for items that do not fit into any of these categories, you can modify the formula like this:

=IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY() + 7, "Yellow", IF([Due Date]@row > TODAY() + 7, "Green", "Gray")))

This adds a fallback "Gray" status, but based on your requirements, the original three-color formula should suffice.

Make sure the column [Due Date] is correctly formatted as a date column, and you should see the desired colour codes updating dynamically.

Kerry St. Thomas

I am assuming here that column DUE DATE is type Date. With that in place, you can make aa Symbol column with Green/Yellow/Red/Gray as the choices, and use the following formula:

=IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row < TODAY(), "Red", IF(NETDAYS(TODAY(), [Due Date]@row) > 7, "Green", "Yellow")))

The first clause makes a GRAY return only if the Due Date is blank. The reason your formulas didn't work is because you were saying essentially "If July 13 is less than 1, do this" and so on - the math don't math. 😀 You need to do math to find out how far removed from the current date your due date is first, and use THAT as your comparison.

Good luck!

Bviola

Thank you @Kerry St. Thomas and @Spoonhead!