RAG Status

Hello! Newbie here :)

I was wondering if someone could help with a formula I'm attempting to put together to calculate a RAG symbol status for individual tasks as well as the overall project plan.

Individual Tasks:
If status is complete = blue
If status is in progress OR not started AND end date is today = yellow
If status is in progress OR not started AND end date is in the past = red
If status is not started AND start date is in the past = red
If status is in progress AND end date is in the future = green
=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "In Progress", [End Date]@row <= TODAY()), "Yellow", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row >= TODAY()), "Green", ""))))

Overall Project: calculates the overall status of the project based on the above.

Thanks in advance!

Find more posts tagged with

Reports

Project Management

project plan

Accepted answers

Paul Newcome

Try this:

=IF(Status@row = "Complete", "Blue", IF(OR([End Date]@row < TODAY(), AND(Status@row = "Not Started", [Start Date]@row < TODAY())), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green")))

All comments

Paul Newcome

Try this:

=IF(Status@row = "Complete", "Blue", IF(OR([End Date]@row < TODAY(), AND(Status@row = "Not Started", [Start Date]@row < TODAY())), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green")))

pjb

Hi @Paul Newcome , thank you for responding! Figured it out in the end, with a few amendments:

=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "In Progress", [End Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Green", ""))))))

Have a great weekend!

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