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

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

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

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

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
Check out the Formula Handbook template!