Conditional Formatting with Multiple Cells - Green, Yellow, Red Symbols

Hello!

I am having some difficulty writing the correct formula for this sheet:

When the "% Complete" is 100%, I would like the status to be a green check. When the "% Complete" is not 100% and the finish date is still in the future, I would like the yellow hold symbol. And when the "% Complete" is not 100% and the finish date is in the past, I would like the red stop sign/no to populate.

Any insight to help me get on the right track would be greatly appreciated!

Thank you,

Rachel

Find more posts tagged with

Accepted answers

John Shane

@moore_r

The formula below checks for boxes in the % Complete column that have a quantity in them and then applies the logic you specified. If the % Complete column is blank, the Staus at a Glance will be blank.

=IF([% Complete]@row = "", "", IF([% Complete]@row = 1, "Yes", IF(AND([% Complete]@row < 1, Finish@row >= TODAY()), "Hold", IF(AND([% Complete]@row < 1, Finish@row < TODAY()), "No", ""))))

Cheers,

John

JamesB

@moore_r

=IF([% Complete]@row=1,"Green",IF(Finish@row>=TODAY(),"Yellow","Red"))

This formula will first check to see if the Percentage is 100, then Green, Since the only way to get yellow or red is based on that primary condition, we do not need to check it again. If the percent is not 100, it will move to the 2nd if statement and check for the next possible condition which is if the finish date is equal to today or in the future, which will then set it to yellow. If neither the % is 100 or the end date is not in the future, then it will default to red.

All comments

John Shane

@moore_r

=IF([% Complete]@row = "", "", IF([% Complete]@row = 1, "Yes", IF(AND([% Complete]@row < 1, Finish@row >= TODAY()), "Hold", IF(AND([% Complete]@row < 1, Finish@row < TODAY()), "No", ""))))

Cheers,

John

JamesB

@moore_r

=IF([% Complete]@row=1,"Green",IF(Finish@row>=TODAY(),"Yellow","Red"))

moore_r

Thank you both for taking time to provide support! It worked like a charm, John!

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.