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"")))))

Tags:

Best Answers

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    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

    [email protected]

  • Gwynneth
    Gwynneth ✭✭
    Answer ✓
    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 (" ").

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    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

    [email protected]

  • Gwynneth
    Gwynneth ✭✭
    Answer ✓

    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

  • Gwynneth
    Gwynneth ✭✭
    Answer ✓

    However, copying and pasting your formula over mine did, work! Go figure. Thanks for your help.

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    No problem, if you have a moment please accept my answer so your question can show up as answered, thanks!

Answers

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    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

    [email protected]

  • Gwynneth
    Gwynneth ✭✭
    Answer ✓
    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 (" ").

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    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

    [email protected]

  • Gwynneth
    Gwynneth ✭✭
    Answer ✓

    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

  • Gwynneth
    Gwynneth ✭✭
    Answer ✓

    However, copying and pasting your formula over mine did, work! Go figure. Thanks for your help.

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓

    No problem, if you have a moment please accept my answer so your question can show up as answered, thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!