Symbols based on Due Dates
Hi there, very new to Smartsheet and I'm hoping that someone can help. I'm using the symbols red, green, yellow and blue as status and assuming the following instances:
If less than or equal to 5 days to due date from today 'Red"
If greater than 5 days but less than 15 days to due date from today "Yellow"
If greater than or equal to 15 days to due date from today "Green"
If due date is blank "Gray"
When I use the below logic, when the due date is blank, the symbol turns red instead of gray.
=IF([Due Date]49 <= TODAY(5), "Red", IF(AND([Due Date]49 > TODAY(5), [Due Date]49 < TODAY(15)), "Yellow", IF([Due Date]49 >= TODAY(15), "Green", IF([Due Date]49 = 0, "Gray"))))
I'd appreciate any feedback. I have a feeling I have an error on the "Red" formula.
Thanks!
Best Answer

You have to cut through your Nested if like a block of cheese and with every slice you rule out certain situations.
I also say If statements should be read from Left to right..
 So if you look at your first check you test for <=5 and set it to Red. and then at the End you check for Zero.
 But Zero is Less than 5 so you are getting Red as it should
 The formula is written in a way that the zero is never evaluated
=IF([Due Date]@row = 0, "Gray", IF([Due Date]@row <= Today(5), "Red", IF([Due Date]@row <= Today(15), "Yellow", "Green")))
 Also as I said we are slicing a block of cheese.
 If you are checking for less than or equal to 5 there is no need to check for greater than 5. you have already cut off the block that is less than or equal to 5 and assigned it to RED
 So you simply need to check for <=15 and so on
Another note: you said you are using Red, Green, Yellow and Blue but you are returning Gray as an option. There is no case that has Grey and Blue..
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Answers

You have to cut through your Nested if like a block of cheese and with every slice you rule out certain situations.
I also say If statements should be read from Left to right..
 So if you look at your first check you test for <=5 and set it to Red. and then at the End you check for Zero.
 But Zero is Less than 5 so you are getting Red as it should
 The formula is written in a way that the zero is never evaluated
=IF([Due Date]@row = 0, "Gray", IF([Due Date]@row <= Today(5), "Red", IF([Due Date]@row <= Today(15), "Yellow", "Green")))
 Also as I said we are slicing a block of cheese.
 If you are checking for less than or equal to 5 there is no need to check for greater than 5. you have already cut off the block that is less than or equal to 5 and assigned it to RED
 So you simply need to check for <=15 and so on
Another note: you said you are using Red, Green, Yellow and Blue but you are returning Gray as an option. There is no case that has Grey and Blue..
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

Thank you so much for the quick response, Brent!
Help Article Resources
Categories
Check out the Formula Handbook template!