Can you please help me figure out what is incorrect with this formula?
=IF([Actual % Complete]@row) = 100, ""Blue"", IF(ISBLANK([End Date]@row)), ""NS"", IF(TODAY() - ([End Date]@row) > 30), ""Red"", IF(TODAY() - [End Date]@row) <=30), ""Yellow"", IF(TODAY() - [End Date]@row) = 0, ""Green"")))))
Best Answers
-
Gwynneth,
Is there a specific reason for the double "" "" around text?
Try this:
=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) <=30), "Yellow", IF(TODAY() - [End Date]@row) = 0, "Green")))))
To make the formula a little simpler, instead of looking for anything that is less than or equal to 30 away, look for anything that equals 0. This way you can just say "If the project is not over 30 days late and not on time, then show me a yellow status".
=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) = 0, "Green", "Yellow")))))
Let me know how it goes!
Lidiya Shutaya
lidiya@ddbconsultants.ca
-
Hi Lydia, According to this Help Article, the double quotes are supposed to be there. In any event, it didn't work when there was one set of quotes, either.
#UNPARSEABLE
Cause
The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.
Resolution
Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").
-
Sorry that did not work!
Took another look through the formula, let's see if it's a parentheses issue, try this:
=IF([Actual % Complete]@row = 100, "Blue", IF(ISBLANK([End Date]@row), "NS", IF((TODAY() - ([End Date]@row) > 30), "Red", IF((TODAY() - [End Date]@row) = 0, "Green", "Yellow"))))
If your Actual % Complete is a percentage, then use =1 instead of =100 because the formula will look at it as a decimal not a percentage. ie 90% = .9
Best,
Lidiya Shutaya
lidiya@ddbconsultants.ca
-
Hi Lydia,
Thanks for your help. I did that, it still didn't work. I also used your copy above with the one set of quotations.
Gwynneth
-
However, copying and pasting your formula over mine did, work! Go figure. Thanks for your help.
-
No problem, if you have a moment please accept my answer so your question can show up as answered, thanks!
Answers
-
Gwynneth,
Is there a specific reason for the double "" "" around text?
Try this:
=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) <=30), "Yellow", IF(TODAY() - [End Date]@row) = 0, "Green")))))
To make the formula a little simpler, instead of looking for anything that is less than or equal to 30 away, look for anything that equals 0. This way you can just say "If the project is not over 30 days late and not on time, then show me a yellow status".
=IF([Actual % Complete]@row) = 100, "Blue", IF(ISBLANK([End Date]@row)), "NS", IF(TODAY() - ([End Date]@row) > 30), "Red", IF(TODAY() - [End Date]@row) = 0, "Green", "Yellow")))))
Let me know how it goes!
Lidiya Shutaya
lidiya@ddbconsultants.ca
-
Hi Lydia, According to this Help Article, the double quotes are supposed to be there. In any event, it didn't work when there was one set of quotes, either.
#UNPARSEABLE
Cause
The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.
Resolution
Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").
-
Sorry that did not work!
Took another look through the formula, let's see if it's a parentheses issue, try this:
=IF([Actual % Complete]@row = 100, "Blue", IF(ISBLANK([End Date]@row), "NS", IF((TODAY() - ([End Date]@row) > 30), "Red", IF((TODAY() - [End Date]@row) = 0, "Green", "Yellow"))))
If your Actual % Complete is a percentage, then use =1 instead of =100 because the formula will look at it as a decimal not a percentage. ie 90% = .9
Best,
Lidiya Shutaya
lidiya@ddbconsultants.ca
-
Hi Lydia,
Thanks for your help. I did that, it still didn't work. I also used your copy above with the one set of quotations.
Gwynneth
-
However, copying and pasting your formula over mine did, work! Go figure. Thanks for your help.
-
No problem, if you have a moment please accept my answer so your question can show up as answered, thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
