Symbols based on Due Dates

cfermin

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!

Brent Wilson

@cfermin

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..

1. 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.
2. But Zero is Less than 5 so you are getting Red as it should
3. 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 Wilson

@cfermin

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..

1. 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.
2. But Zero is Less than 5 so you are getting Red as it should
3. 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..

cfermin

Thank you so much for the quick response, Brent!