=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"")))))
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
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.
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,
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!
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
