Rag Status for end date and % complete

Hi there,

Could somebody help me with this formula for a rag status please?

The one I'm using at the minute is not picking up red statuses.

=IFERROR(IF([Percentage Complete]@row = 1, "Blue", IF(AND([Percentage Complete]@row < 1, [Finish Date]@row > TODAY()), "Green", IF(AND([Finish Date]@row < TODAY(1), [Finish Date]@row < TODAY(3)), "Yellow", "Red"))), "")

Thanks!

Jez

Find more posts tagged with

Formulas

Accepted answers

All comments

Darren Mullen

@Jez Akali This issue is here: IF(AND([Finish Date]@row < TODAY(1), [Finish Date]@row < TODAY(3)), "Yellow")

Both of these AND conditions will always be true if the finished date is more than 3 days in the past. You would need to do something like AND([Finish Date]@row < TODAY(1), [Finish Date]@row > TODAY(4))

Jez Akali

Thanks @Darren Mullen unfortunately I'm still getting the same issue

Genevieve P.

Hi @Jez Akali

Just a brief note - the numbers in your TODAY function should be negative if you're looking for days in the past. Try this:

=IFERROR(IF([Percentage Complete]@row = 1, "Blue", IF([Finish Date]@row > TODAY(), "Green", IF([Finish Date]@row > TODAY(-4), "Yellow", "Red"))), "")

The formula will only move to the Yellow statement if the % complete is less than 100%, and if the Finish Date is not in the future. This means we only need to check and see if the date is within the last 3 days (or greater than 4 days ago).

Cheers,

Genevieve

Jez Akali

That's great, thank you so much @Genevieve P. !

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.