How to calculate RAG status in MS-Project ®

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?

How to customise MS-Project® text fields to calculate RAG Status

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 (see figure 1).

Figure 1: MS-Project custom text formula dialogue box

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:

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:

      ([% 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” (see figure 2).

Entering the Auto RAG formula in the custom text field formula box
Figure 2: Entering the Auto RAG formula in an MS-Project® custom text field 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 (figure 1), but this time using the Graphical Indicators option, which gets you here:

Specifying graphical indicators for RAG status in an MS-Project® custom text dialogue box
Figure 3: Specifying graphical indicators for RAG status in an MS-Project® custom text dialogue box

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 have now introduced schedule to 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, Pragmatic PMO can help. Why not take a look at our “Savvy scheduling” service, and if that looks interesting, schedule a free 30-minute consultation to discuss how we can help you?

Copyright © Ken Burrell, Pragmatic PMO Ltd 2017

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

Author: Ken Burrell

Ken Burrell is a Programme and Portfolio Office (PMO) Professional, who through his company Pragmatic PMO makes targeted improvements to PMO practices to add value to Projects, Programmes and Portfolios. He provides senior management with the analysis they need to make decisions, and gives project and programme managers the support they need to deliver solutions.

11 thoughts on “How to calculate RAG status in MS-Project ®”

  1. Interesting read Ken. It’s something I’ve used to try and engage policy people in planning. Great success when I changed the RAG ratings to smiley (G) and grumpy (R) faces. No-one wanted a grumpy face!

    1. Hi Lorna, thanks for reading the article and taking the time to comment. I’m glad it struck a chord with you. I have also used faces to indicate RAG status, mainly to overcome potential issues with red/green colour blindness (more common than you’d think) and monochrome printing / photocopying (prohibitively expensive a few years ago, less of a problem nowadays). Things like emoticon faces get a somewhat mixed reaction in corporate circles (e.g. financial services). I think it’s a friendly and visual (especially if the faces are also coloured Red 🙁 / Amber 😐 / Green 🙂 ) way to give a quick indication of how things are going. And you’re right: no-one wants to get a grumpy face!

  2. hello,

    i have used the Formula bit it did not work. i do not know what is the reason.

    1. Hi Dieaa, thanks for commenting and please accept my apologies for the extended delay in replying. Your comment was buried in a queue under a whole load of spam comments and I have only just seen it. As I replied to Kim, there was a typo in the formula that I have now corrected. It should work now. Regards, Ken

  3. Hi Ken,
    I’m getting a syntax error when I use the formula provided. It isn’t liking the %Complete in the first line. Any suggestions?

    1. Hi Kim, thanks for commenting and please accept my apologies for the extended delay in replying. Your comment was buried in a queue under a whole load of spam comments and I have only just seen it. The reason that the formula didn’t work is that there was a typo: there should have been a space between “%” and “Complete”. I have fixed that in the post and the formula should work now. Regards, Ken

  4. The formula still does not work. The highlight is on “C” in the first row:

    ([% Complete] = 100), “C”,

    1. Hi Bruna, thanks for commenting, and sorry the formula doesn’t work for you as presented. But as you have told me where the error highlight is, I think I know what’s going on here! In the blog post text, the text editor has autoformatted the double quotes as “smart quotes” (which look like 66 and 99; typographically correct for written text, not so good for MS-Project formula code!). If you exchange the “smart quotes” for “plain text double quotes” then the formula should work. I have updated the article accordingly. Let me know if you still have problems. Regards, Ken

  5. Hi Ken, many thanks for this. You still have a tiny error in your code. You should have ([Baseline Finish]<Now()), rather than ([Baseline Finish],Now()). Once fixed, it's suitable for framing. A great help!



    ([% Complete] = 100), "C",
    ([Baseline Finish] [Baseline Finish]), “A”,
    ([Finish] <= [Baseline Finish]), "G",
    True, "ERROR!"

  6. Awesome! Worked easily for me first time – really appreciate this.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.