If, and & if logic unprasable

namita

Can you see what is wrong with this syntax? I am getting an unparsable error.

=IF(AND([Status]@row <> "Complete", [End Date]@row >Today()), "Red", IF(AND(Status@row <>"Complete", [End Date]@row < Today(-7)),"Yellow", IF(AND(Status@row = "In Progress", [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", [Start Date]@row > TODAY()), "Blue”, IF(Status@row="Complete","Green", "Green")))))

Paul Newcome

Try retyping the closed quote after "Blue" in Smartsheet itself.

The slanted quotes are called "smart quotes" which (ironically) are not recognized as valid characters in Smartsheet formulas. You need the ones that are straight up and down that are generated here in the Community, within Smartsheet, or in text editors such as Notepad (Word uses the "smart" ones by default).

On a separate note, this can also be simplified by leveraging the logic inherent with nested IF statements that says to get to the second IF then the first must be false. If you start with Status@row = "Complete", then every IF after that already assumes it is not "Complete" and you can get rid of that argument as well as the AND functions.

I also notice you have some conflicting arguments tucked in.

AND([Status]@row <> "Complete", [End Date]@row >Today()), "Red"

AND(Status@row = "In Progress", [End Date]@row > TODAY()), "Green"

In the above, you will never get the "Green" output between the two because "In Progress" is not equal to "Complete". Assuming the End Date is in the future (greater than today), the "Red" argument will trigger as true and output "Red", never making it to the portion that evaluates if the Status is "In Progress".

If you want to list out your requirements in order of most important, I'm sure we could work together to get you a formula that functions exactly as expected and as efficiently as possible.

For example:

Status is "Complete" = "Green"

Start Date is in the future = "Blue"

End Date is in the past = "Red"

End Date is in the next 7 days = "Yellow"

End Date is more than 7 days in the future = "Green"

Paul Newcome

Try retyping the closed quote after "Blue" in Smartsheet itself.

The slanted quotes are called "smart quotes" which (ironically) are not recognized as valid characters in Smartsheet formulas. You need the ones that are straight up and down that are generated here in the Community, within Smartsheet, or in text editors such as Notepad (Word uses the "smart" ones by default).

On a separate note, this can also be simplified by leveraging the logic inherent with nested IF statements that says to get to the second IF then the first must be false. If you start with Status@row = "Complete", then every IF after that already assumes it is not "Complete" and you can get rid of that argument as well as the AND functions.

I also notice you have some conflicting arguments tucked in.

AND([Status]@row <> "Complete", [End Date]@row >Today()), "Red"

AND(Status@row = "In Progress", [End Date]@row > TODAY()), "Green"

In the above, you will never get the "Green" output between the two because "In Progress" is not equal to "Complete". Assuming the End Date is in the future (greater than today), the "Red" argument will trigger as true and output "Red", never making it to the portion that evaluates if the Status is "In Progress".

If you want to list out your requirements in order of most important, I'm sure we could work together to get you a formula that functions exactly as expected and as efficiently as possible.

For example:

Status is "Complete" = "Green"

Start Date is in the future = "Blue"

End Date is in the past = "Red"

End Date is in the next 7 days = "Yellow"

End Date is more than 7 days in the future = "Green"

namita

Thanks a lot, it worked! AND([Status]@row <> "Complete", [End Date]@row < Today()), "Red" - that should have been less than. Thank you for catching that. Corrected that logic.

Paul Newcome

Happy to help. 👍️

This would be my suggestion for a "simplified" version of the formula:

IF(Status@row = "Complete", "Green", IF([Start Date]@row > TODAY(), "Blue", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green"))))

namita

Paul, Sorry for the late reply but this is much cleaner!! Thanks a lot!!