RAG Status by Percentage Complete and today's date

Query

I have a formula for RAG status which I've been using to manage performance across multiple projects looking at the columns end date, % complete, and referencing today's date. However it is not working as planned and wanted to ask for some help please.

I would like the formula to simply change the RAG status for a tasks as follows:

Complete or up to 5% behind schedule = green;

5-10% behind schedule = amber;

11% or more behind schedule = red.

Current Status

The way the formula is currently working is as follows: if the task is ahead of schedule or complete it is green; if the task is behind schedule but the due date hasn't passed it is amber, if it is behind schedule and the due date has passed it is red.

Current Formula

IF(AND(TODAY() > [End Date]@row, [% Complete]@row < 0.9), "Red", IF([% Complete]@row >= ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)), "Green", "Yellow"))))

Any help would be excellent, thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@DomG

If I understand correctly, you're calculating the % behind or ahead of schedule by counting the number of days since the start date and dividing that by the scheduled number of days in the project, yes? So for instance, if I had a project starting 8/1/22 and ending 8/21/22, and today is 8/22/22, I'm at 1.05, or 5% behind schedule. But if my end date was 8/23/22, today I'd be at .95, or 5% ahead of schedule.

Looking at your desired logic:

Anything 100% complete OR up to 5% behind schedule should be green.

Anything not 100% complete, AND more than 5% but only up to 10% behind schedule, should be yellow.

Anything not fitting the above criteria (i.e. anything not yet complete and over 10% behind schedule,) should be red.

So let's try this:

=IF(OR([% Complete]@row = 1, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) <= 1.05), "Green", IF(AND([% Complete]@row < 1, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) > 1.05, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) <= 1.1), "Yellow", "Red"))

DomG

Many thanks for coming back to me Jeff.

Unfortunately, the formula hasn't worked. When testing in one of the projects, it is showing the project, which is 30% behind schedule as green whereas based on the criteria below it should be Red. The logic you outline above is correct, however

I have a baseline column which I compare against, Planned % Complete with a simple formula as follows:

=IF(TODAY() >= [End Date]@row, 1, IF(TODAY() <= [Start Date]@row, 0, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)))

As you will see from the screenshot - the project should be 44% complete, but it is 11% complete and showing as Green with your formula.

Any other suggestions would be super helpful.

Smartsheet RAG Score Fomula - Jeff V1.JPG

The Red / Amber / Green criteria are as follows:

Green

Up to 5% behind schedule vs Planned % Completion OR ahead of schedule OR complete = Green

Amber

Between 90%-95% behind planned % completion = amber

Red

<90% behind planned percentage completion = red

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.