Part of a series on customising Microsoft® Project® to make working with project schedules more useful.

So you’ve created a useful programme schedule, and you are now working on keeping it on the right track, reviewing the plan regularly with the Programme Manager and Project Managers.

You will probably review the programme schedule regularly – focussing on on items with Red or Amber RAG status –  but how do you use MS-Project® to ensure that you don’t skip items that probably should have been marked amber or red?

This post describes how to calculate RAG status in MS-Project ® by customising a text field to performing some simple tests and calculations with dates.

So why would you want to use this approach, how do you do it, and what’s in it for you as a result?

The Why

RAG status is often allocated to project tasks based on the intuition or “gut feel” of the project manager. But too much subjectivity in status reporting can lead to watermelon or green-side-up reporting, in which project status is reported more favourably than it probably should be. The more objective approach presented here introduces schedule automation to calculate RAG status in MS-Project ®, assigning a RAG rating to individual project tasks based on calculation.

This approach can’t account for subjective things like project risks (that takes professional judgement), but it can give a useful “starter for ten” RAG status assessment onto which the PM can layer factors such as risk, or to act as the starting point of a discussion.

The How

Approach: Logic tests

To calculate RAG status in MS-Project ®, first devise a set of criteria that you will use to determine RAG status, based on the rules or criteria in place for your project or programme. Your central PMO may have some criteria, or you may devise your own. Some simple date-based may look something like this (this assumes the plan has been baselined):

• Red = Item that should be complete by now but isn’t
• Amber = Item forecast to finish later than baseline finish date
• Green = Item forecast to finish on baseline finish date or earlier

You need to formulate the tests as a mathematical expression that when evaluated will return a TRUE or FALSE result. For example, to test whether an item should have finished by now, test for [Forecast finish date] < [Today’s date]. If the answer is TRUE, the item should have finished by now.

These tests can be made as simple or complex as you like, for example you could enhance the Red test so that Red returns true if the item (is forecast to finish earlier than today’s date) OR (is on the Critical path, and is forecast to finish later than baselined) OR (is on the critical path and has not yet been baselined).

After testing for item completion (because if the item is complete then none of the other tests apply) these tests will be applied in order of decreasing severity (because an item that triggers a Red condition would probably trigger all the Amber conditions too).

Implementing the tests

To calculate RAG status in MS-Project ®, first pick an empty custom task text field (in MS-Project 2016, select Project, Custom Fields), rename it something like “Auto RAG” and click on Formula:

The MS-Project Switch() function applies a number of tests and performs an action for the first test it comes to that returns a TRUE result. The format is:

Switch(
TestForCompleteCondition, ResultIfTestIsTrue (“C”),
TestForRedCondition, ResultIfTestIsTrue (“R”),
TestForAmberCondition, ResultIfTestIsTrue (“A”),
TestForGreenCondition, ResultIfTestIsTrue (“G”),
TestThatAlwaysReturnsTrue, ResultIfTestIsTrue (ErrorMessage),
)

The TestThatAlwaysReturnsTrue at the end “catches” the condition where none of the previous tests return a True result. Seeing the “ERROR!” result should prompt you to check your tests (or add some more conditions to catch other scenarios).

In “coded” format, such a series of tests would look like this:

Switch(
      ([% Complete] = 100), "C",
      ([Baseline Finish] < Now()), "R",      ([Finish] > [Baseline Finish]), "A",
      ([Finish] <= [Baseline Finish]), "G",
      True, "ERROR!"
 )

I have formatted this to make it more readable. Be aware that MS-Project will strip out the spaces and returns, so the formula will look a lot less readable if you ever have to come back to edit it.

Put this in the “formula box”:

Once you have a text RAG value, you will probably want to display that as a graphical indicator to make reds and ambers easier to pick out in a long plan. This is done from the same Customise Fields dialogue box as before, but this time using the Graphical Indicators option, which gets you here:

The approach is to test whether the value in the field “equals” each of the RAG status values and to assign an appropriate graphical symbol to each.

The What(’s in it for you)

Congratulations! You can now calculate RAG status in MS-Project ®, so you can tell you what the RAG status of an item should be, based on a rigid interpretation of whatever rules you have given it, ready to have overlaid upon it any other factors the PM knows about. What approaches do you use to calculate RAG status in MS-Project?

This approach comes to you from my experience of getting it wrong and coming up with a better way.

If you would like to have the benefits of an approach like this but would prefer not to get bogged down in the detail, The PMO Professionals can help. Why not take a look at our “Savvy scheduling” service, and if that looks interesting, get in touch to discuss how we can help you?

® Microsoft and Project are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.