IF statement using AND to generate colored symbol

Hello!

Can somebody please help with the following formula? I am trying to generate the colored symbols (Green,Yellow,Red) based on the percentage of tasks complete along with the go live date. I am able to get a single If statement to generate the symbol but am failing at the AND one. Below is the formula that I'm using along with the conditions for the different colors.

Green: If Percentage Complete is greater than 60% and the event end date is less than 30 days

Yellow: If Percentage Complete is greater between 30- 60% and the event end date is less than 30 days

Red: If Percentage Complete is less than 30% and the event end date is less than 30 days

=IF(AND([Percentage Complete]@row >= 0.6, [Event End Date]@row < TODAY(+30)), "Green", IF(AND([Percentage Complete]@row = <0.3 < 0.6, [Event End Date]@row < TODAY(+30)), "Yellow"),IF(AND([Percentage Complete]@row<.3, [Event End Date]@row< TODAY(+30)) "Red"))

Find more posts tagged with

Formulas

Accepted answers

All comments

Bassam Khalil

Hi @Elizabeth Smiley

Hope you are fine, please try the following formula and convert it to column format formula:

=IFERROR(IF(OR(ISBLANK([Percentage Complete]@row), ISBLANK([Event End Date]@row)), "", 
IF(AND([Percentage Complete]@row > 0.6, [Event End Date]@row < TODAY(30)), "Green", 
IF(AND([Percentage Complete]@row < 0.6, [Percentage Complete]@row >= 0.3, 
[Event End Date]@row < TODAY(30)), "Yellow", IF(AND([Percentage Complete]@row < 0.3, 
[Event End Date]@row < TODAY(30)), "Red")))), "")

The following screenshot shows the result:

But i think you need to recheck your conditions because let me say you have event date on 1-Oct-2021 if you but any Percentage Complete you will get Blank.

hmiller22

When I change number of days out to trigger the color change, the formula returns a blank cell... Not sure what to do. I replaced the 30s with 3s. I have also tried +3.

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