Traffic light formula problem with two conditions

Dear

I have a column of% Complete (% Complete) and a Task Completion (ENDDATE)

I would like to generate the following conditions:

1. Green if 100% of the task is complete and it has been given on the estimated date (which is equal to or less than today)

2. Yellow if it is between 51% and 99% complete as of today

3. Red if the advance is less than 50% and the date is today

Your help is greatly appreciated.

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Alfredo Alva

Hope you are fine, you want to do it in conditional formatting to highlight the hole row or you want to do it using a symbols column with RGY.

Alfredo Alva

Hi...yes RGY

Genevieve P.

Hi @Alfredo Alva

Try the following formula:

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 0.5, [ENDDATE]@row <= TODAY()), "Red", "Yellow"))

These are the instructions:

Green: IF([% Complete]@row = 1, "Green"

If the % Complete is 100%, turn Green (regardless of End Date)

Red: IF(AND([% Complete]@row <= 0.5, [ENDDATE]@row <= TODAY()), "Red"

If the % Complete is less than or equal to 50%

AND

If the End Date is either today or in the past

Yellow: Otherwise, Yellow.

If the % Complete is not 100%, and not less than 50% with a date in the past

(Aka - if the % is ongoing and the end date is in the future)

Let me know if this returns the correct colours for your process!

Cheers,

Genevieve

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.