Am stuck with an IF(AND formula

=IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(), "Red", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY(), "Amber", "Green"))))

which should mean (in my dreams :))

if under 100% and the date is past, then red

if under 100% and the date is today and future, then amber

otherwise green

Where am I wrong please

Find more posts tagged with

Accepted answers

Jeff Reisman

@KarineMul22_TPg

I think you're close, just have some misplaced parentheses and incorrect operators. Let's see if this works:

=IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row >= TODAY()), "Amber", "Green"))

All comments

KarineMul22_TPg

I also tried

=IF(AND([End Date]@row >= TODAY(), [% Complete]@row = 1, "Green", IF(AND([End Date]@row >= TODAY(), [% Complete]@row > 0, "Amber", "Red"))))

Jeff Reisman

@KarineMul22_TPg

I think you're close, just have some misplaced parentheses and incorrect operators. Let's see if this works:

=IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row >= TODAY()), "Amber", "Green"))

KarineMul22_TPg

Thank you, that works and makes perfect sense - now I have a new problem! as I want my Green, Amber, Red to show as the coloured dots equivalent but some do and some still show the Name of colour instead, whereas the column property is restricted to symbols

Any idea please?

KarineMul22_TPg

Sorry, I 've found out! It's because I use "Amber" instead of "Yellow", doh!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.