Dynamically update Health column using Symbols based on proximity to Due Date
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")))
Best Answers

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 nestedIF
statements. Also, when using date calculations, we can leverage theTODAY()
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:
 If the due date is before today, it will display "Red".
 If the due date is within the next 7 days (including today), it will display "Yellow".
 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 threecolor 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. 
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!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response  "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Answers

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 nestedIF
statements. Also, when using date calculations, we can leverage theTODAY()
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:
 If the due date is before today, it will display "Red".
 If the due date is within the next 7 days (including today), it will display "Yellow".
 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 threecolor 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. 
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!
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response  "Insightful"or "Awesome" reactions are much appreciated. Thanks!

Thank you @Kerry St. Thomas and @Spoonhead!