If, and & if logic unprasable
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")))))
Best Answer
-
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"
Answers
-
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"
-
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.
-
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"))))
-
Paul, Sorry for the late reply but this is much cleaner!! Thanks a lot!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!