Yellow Code Not Working in Formula

delaurellc

I have the underlying code in a sheet to look at specific items in a row. The Red and Green Code is working as expected by my yellow code is not. I am assuming it has to do with the way I have sestup the today function I have tried both =TODAY(2) and >=TODAY(2) niether seems to work.

IF(AND([Due Date]@row = TODAY(2), Status@row <> "Complete"), "Yellow"

I need it to do the following if the due date is less than today Red. if it is today tomorrow and the next day it is Yellow, if it is in the future three days or more green. As I said the red and green work fine.

Full Code

=IF(AND([Due Date]@row < TODAY(), Status@row <> "Complete"), "Red", IF(AND([Due Date]@row = TODAY(2), Status@row <> "Complete"), "Yellow", "Green"))

Thanks,

Caroline

Paul Newcome

Try this:

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

The main issue with your formula is that you are saying to only flag yellow when the due date is equal to TODAY(2) instead of less than or equal to.

Paul Newcome

There are a number of ways to do this. The most secure way would actually mean a restructure and an OR statement:

=IF(Status@row <> "Complete", IF(OR([Due Date]@row = "", [Due Date]@row.= TODAY(3)), "Green", IF([Due Date]@row< TODAY(), "Red", "Yellow"))

I moved the Green argument to be first because sometimes Smartsheet will read a blank cell as being less than a non-blank cell. If we had left it in the original order, this would have meant it would flag as true on the red argument when the due date was blank.

Of course there are a number of other ways to do it, but this would be my personally preferred method.

Paul Newcome

Try this:

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

The main issue with your formula is that you are saying to only flag yellow when the due date is equal to TODAY(2) instead of less than or equal to.

delaurellc

Hi Paul.

anything less than today should be red

anything today tomorrow and the next day yellow so today+2 days

anything 3+ days should be green

Caroline

Paul Newcome

Right. Did you plug it in and try it?

When Smartsheet is evaluating a nested IF statement, it reads from left to right and stops on the first true value. So if it makes it to the second IF then it is already known that the first is not true. We can leverage this by saying "if it is less than today then output red, if it is greater than or equal to today +3 then output green, and everything else is yellow."

If it passes by both red and green in the above, then it must be greater than or equal to today by passing red but also less than today +3 by passing over the green.

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

If it makes it past the bold portion, then the date MUST be either today or in the future.

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

If it makes it past his bold portion, then we already know it is greater than today because it made it this far to begin with, and we know it is less than today +3 because it isn't flagging as true. So we output yellow.

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

The bold portion here is basically saying to run the nested RYG IF formula if the Status is not Complete. This saves us from having to repeat in with an AND function for each of the colors.

Using the same logic as outlined above where we read from left to right and stop on the first true value, the same outcome could be accomplished using this formula:

=IF(Status@row <> "Complete", IF([Due Date]@row< TODAY(), "Red", IF([Due Date]@row<= TODAY(2), "Yellow", "Green"))

Or this one:

=IF(AND(NOT(Status@row = "Complete"), [Due Date]@row< TODAY()), "Red", IF(AND(NOT(Status@row = "Complete"), [Due Date]@row>= TODAY(), OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1), [Due Date]@row = TODAY(2))), "Yellow", IF(AND(NOT(Status@row = "Complete"), [Due Date]@row>= TODAY(3)), "Green")))

Or quite a few other ways to get the same outcome. I personally just like to establish the far ends (in this case red and green) and just say that anything that isn't one of those must be in the middle.

delaurellc

Paul,

This is good, but can you tell me how to add one more item to this code

If the due date is blank it needs to be green

Where would I add this?

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

Caroline

Paul Newcome

There are a number of ways to do this. The most secure way would actually mean a restructure and an OR statement:

=IF(Status@row <> "Complete", IF(OR([Due Date]@row = "", [Due Date]@row.= TODAY(3)), "Green", IF([Due Date]@row< TODAY(), "Red", "Yellow"))

I moved the Green argument to be first because sometimes Smartsheet will read a blank cell as being less than a non-blank cell. If we had left it in the original order, this would have meant it would flag as true on the red argument when the due date was blank.

Of course there are a number of other ways to do it, but this would be my personally preferred method.

delaurellc

Not working

I have tried the blank this way multiple times and it does not work

[Due Date]@row = ""

I have always had to sue the ISBLANK funtion but not sure how to add to this code, no matter what I do not working 🤯

delaurellc

Paul,

I was able to get it working with your code working for some reason it copied a period where the greater than symbol was suppost to be.

Thank you as always,

Caroline

Paul Newcome

Happy to help. 👍️

When you type something like

[Due Date]@row = ""

into a formula and it doesn't work, exactly what are you typing, and are you using some other program such as Word to type out formulas?

The "" part is quote quote with no space. If you are typing quotes in another program, that program could be using what are called "smart quotes". If you do a search here in the Community, you will find quite a few posts explaining them. I just haven't had enough coffee yet this morning to make it coherent. Haha.