An Improved Calculated Status Indicator for MS Project

An improved version of this article is included in my free e-book, MS Project Hacks. Download the book and a sample MS Project file demonstrating the techniques shown here.

About a year ago, I posted an explanation of how to create a “stop light” status indicator using calculated fields in MS Project.  It has since become the most widely read, referenced, and commented-on post I’ve ever made.  So, based on some of those comments and a request from Marlene Robertson, I’ve decided to post an update.  Here’s what it does:

  • For zero duration tasks (milestones)
    • If % Complete = 100, return Blue
    • If the scheduled finish is at least seven days away, return Clear
    • Otherwise, if % Complete is < 80, return Red
    • If % Complete is between 80 and 99, return Yellow
  • For non-zero duration tasks
    • If % Complete = 100, return Blue
    • If it’s not scheduled to start yet, return Clear
    • If it’s past the scheduled finish date, return Red
    • If % complete >= the prorated expected completion, return Green
    • Otherwise, return Yellow.

That “prorated expected completion” is calculated using the project calendar.  If the percent complete is less than the number of days since the task was scheduled to begin, divided by the task duration, it will be Yellow; otherwise, it will be Green.  Keep this in mind for later, when we’ll talk about summary tasks.

To add this to your project schedule, right click on the column where you want to insert the status indicator.  Click “Insert Column” and then select Number 1.  Right click on the newly inserted column and select “Custom Fields.”  From here, you can rename the field (I chose “St” to keep it brief).  Under custom attributes, click on the radio button for Formula.  You’ll get a warning message; that’s fine, because you really do want to calculate the values. CustomFields

Then click on the Formula button, and past in the following formula, if you are using MS Project 2007 or earlier:

IIf([Duration]=0,(IIf([% Complete]=100,5,IIf([Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80, 3,IIf([Finish]<Now()+7 And [% Complete]<80,4, 1)))),IIf([% Complete]=100,5,IIf([% Complete]>=100*(Abs(ProjDateDiff([Start],Now())/ProjDateDiff([Start],[Finish]))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3), 4)))))

If you are using MS Project 2010 or later, the field names have changed.  Use the following formula:

IIf([Scheduled Duration]=0,(IIf([% Complete]=100,5, IIf([Scheduled Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80,3,IIf([Scheduled Finish]<Now()+7 And [% Complete]<80,4,1)))),IIf([% Complete]=100,5, IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))))

Formula 2Click OK, and then click on the Graphical Indicators button.  This is where you choose the colors to display.  In each row, for Test select “is greater than or equal to.”  Associate 4.5 with Blue, 3.5 with Red, 2.5 with Yellow, 1.5 with Green, and 0.5 with Clear. Note that you only want these indicators to appear for non-summary rows, since the % Complete field isn’t accurate for summary rows. More about that in a moment.GraphicalIndicators 2Then click OK.  Your project plan should look like this: ProjectPlan 2On August 25, Task 1 is Blue because it’s complete.  Task 2 is Yellow because it’s two days past the start date, it’s a three day task, and it’s only 50% complete.  Task 3 is Green because two days have passed, the task was scheduled for five days, and it’s 75% complete.  If the task were less than 40%, it would be Yellow.  Task 6 is Red because it’s incomplete, and past the Finish date.   The start date for Child Task C is in the future, so it’s Clear.  Note that “days” is defined using the project calendar, so it only uses working days.

You’ll see that no indicator is shown for Task 5, which is the summary task for Child Tasks A, B, and C.  For automatically scheduled summary tasks, MS Project shows the Start date as the earliest Start date among its children, and Finish date as the latest Finish date.  It calculates Duration for its child tasks as the difference between the two.  In the simple example above, the child tasks are scheduled in the sequence A, B, C so that the summary task duration actually reflects the sum of the durations of the individual tasks, ten days.  Task A is scheduled for two days, and is 25% complete; Tasks B and C are not started.  Based on this, summary Task 5 calculates 5% complete, which is accurate.

Now consider if Task A and Task B were scheduled to start on the same date.  The Duration for Task 5 would be calculated as eight days, although it still represents ten work days of effort.  After two days, you would expect Task A to be complete, Task B to be at 50%, and Task C to not yet have begun, for a total of four days of effort.  Thus, anything less than 40% complete for the summary task should appear Yellow, but the formula will show Green at 25%.  So rather than display a potentially inaccurate result, we simply don’t show stop lights for the summary tasks.

Give it a try, and leave a comment if you find it useful, or find a bug, or think of an enhancement.

This entry was posted in Theory and Practice and tagged , , by Dave Gordon. Bookmark the permalink.

About Dave Gordon

Dave Gordon is a project manager with over twenty five years of experience in implementing human capital management and payroll systems, including SaaS solutions like Workday and premises-based ERP solutions like PeopleSoft and ADP Enterprise. He has an MS in IT with a concentration in project management, and a BS in Business. He also holds the project management professional (PMP) designation, as well as professional designations in human resources (GPHR and SPHR) and in benefits administration (CEBS). In addition to his articles and blog posts, he curates a weekly roundup of articles on project management, and he has authored or contributed to several books on project management.

54 thoughts on “An Improved Calculated Status Indicator for MS Project

  1. Hi Dave,

    I’m trying to use the formula for calculating status using graphical indicators but I am getting an error when I copy and paste. It says it’s a syntax error or an error due to an unrecognized field or function name. Specifically it highlights this as the problem – [% Complete<80,4, 1)))),IIf([% Complete]. Could you help me out with what the problem might be? I am using MSP 2007.

  2. Hi Jesse,

    It looks like you’re missing the “]” at the end of the “[% Complete]”. Because of the space character in the middle of the field name, it has to be enclosed in brackets. Give it a try and let me know if that fixed the problem.

  3. For some reason, dates that are set to start in the future are turning up with a red status, eventhough I have followed your instructions to the letter. Any ideas??

  4. Hi Mary,

    Right click on the column and select Customize Fields. At the bottom, under Values to Display, select Data. A future-dated task should show a 1. If you get a different value, the problem is in the formula; if you get the 1, then you might have the graphical indicators out of sequence. Remember to arrange them from highest to lowest values.

  5. Thank you, Dave. This has been really helpful.
    Below is a slightly different version comparing [Finish] vs [Baseline Finish] that I am using.

    IIf([Scheduled Duration]>=0,(IIf([% Complete]=100,5, IIf([Finish]=[Baseline Finish] And [% Complete]>0,2, IIf([Finish]>[Baseline Finish] And [% Complete]=80,3, IIf([Finish]>[Baseline Finish] And [% Complete]<80,4,1))))))

  6. Hi Gary,
    Many thanks for the formula, this has worked wonders for my plan. But was wondering if you can help me with a small tweek.
    I am trying to generate the following for the milestone formula:

    -If % Complete = 100, return Blue
    -If the scheduled finish is at least seven days away, return Clear
    -Otherwise, if % Complete is <80, return Yellow
    -If it’s past the scheduled finish date (regardless of the %), return Red

    Trying to find a way to incorporated into the rest of the formula
    If you can crack this that'll be perfecto!
    Thanks…as always

  7. Hi Rishi,

    To get this behavior:

    If Milestone (Duration = 0)
    If Complete = 100%, return 5 (Blue)
    Else If Finish < Today, return 4 (Red) Else If Finish > 7 days away, return 1 (Clear)
    Else If % Complete < 80%, return 3 (Yellow) Else return 2 (Green) Else If Complete = 100%, return 5 (Blue) Else If prorated complete > scheduled complete, return 2 (Green)
    Else If Finish > Today
    If Start > Today, return 1 (Clear)
    Else return 3 (Yellow)
    Else return 4 (Red)

    Use this formula:

    IIf([Duration]=0,
    (IIf([% Complete]=100,5,
    IIf([Finish]=Now()+7,1,
    IIf([% Complete]<80,3,
    2))))),
    IIf([% Complete]=100,5,
    IIf([% Complete]>=100*(Abs(ProjDateDiff([Start],Now())/ProjDateDiff([Start],[Finish]))),2,
    (IIf([Finish]>Now(),IIf([Start]>Now(),1,
    3),
    4)))))

  8. Hi Gary
    It an excellent article and tip
    I am using MS Project 2007 but when I pasted your formulae below i got an error message that says “The formulae contains a syntax error or contains a reference to an unrecognized field or function name
    IIf([Duration]=0,(IIf([% Complete]=100,5,IIf([Finish]<Now()+7 And [% Complete]=80, 3,IIf([Finish]<Now()+7 And [% Complete=100*(Abs(ProjDateDiff([Start],Now())/ProjDateDiff([Start],[Finish]))),2,(IIf([Finish]>Now(),IIf([Start]>Now(),1,3), 4)))))
    Your assistance is greatly appreciated

  9. Hi Hari,

    You’ve made a number of changes to the original formula, and I’m not sure what you are trying to accomplish. One thing to note: any time you use brackets around a field name, you need to have both [ and ]. In your formula, you have [% Complete=100*(Abs. This isn’t valid, and is part of the reason you are getting an error. Similarly, you need to have matching on all parentheses. It looks like you are missing two close parentheses. Start over with the basic formula and get it to work. Then make any changes to get the behavior you want. If you want help getting something to work, reply with a description, like I provided in my response to Rishi.

  10. Dave, this is almost what I’m looking for, but my needs are a bit simpler and could use your help. I’m using Project 2007 and want to accomplish the following:

    Milestones:
    – If 100% complete, return Blue
    – Else return clear

    Tasks:
    – If 100% complete, return Blue
    – If Today 1% (in process), return Green
    – If Today < Finish date and % complete = 0% (waiting to start), return Yellow
    – If task is incomplete and past finish date, return Red
    – If Today < Start date and % complete = 0% (not started, future event), return Clear

    Thanks!

  11. Sorry… left out a bit of info…

    Tasks:
    – If 100% complete, return Blue
    – If Today < Finish date and % complete = 1% (in process), return Green
    – If Today < Finish date and % complete = 0% (waiting to start), return Yellow
    – If task is incomplete and past finish date, return Red
    – If Today < Start date and % complete = 0% (not started, future event), return Clear

  12. Hi Ken,

    I re-organized your logic a bit, to make it easier to implement. Hopefully it will make the actual formula more readable.

    Milestones:
    – If 100% complete, return Blue
    – Else return clear

    Tasks:
    – If 100% complete, return Blue; Otherwise
    – If Today < Start date and % complete = 0% (not started, future event), return Clear - If Today <= Finish date and % complete = 0% (waiting to start), return Yellow; Else return Green - Otherwise, the task is incomplete and past finish date, so return Red Here's the formula: IIf([Duration]=0,IIf([% Complete]=100,5,1), IIf([% Complete]=100,5, IIf([Start]>Now() And [% Complete]=0,1,
    IIf([Finish]>=Now(),IIf([% Complete]=0,3,2),4
    ))))

  13. Hi, I am getting most of my future tasks in red status and only some in clear?? This is the formula:
    IIf([Scheduled Duration]=0,(IIf([% Complete]=100,5,IIf([Scheduled Finish]<Now()+7 And [% Complete]=50,3,IIf([Scheduled Finish]<Now()+7 And [% Complete]=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))))

  14. Hi Khush,

    It appears that you have a piece missing in the middle. I’ll create a new post with pointers for creating your own formula, and then you can use that as a basis for correcting the problem. If it’s still not working, reply to the new post.

  15. Hi Dave,

    Can you give me the coles notes version of the formula you responded to Rishi with? Thanks!

  16. Hi Kush,

    I posted another MS Project example, to track qualitative risk. It includes a button that allows you to download a sample file, with an improved status calculation formula. It might be easier for you to just use that sample file as your starting point and make small changes, until you get what you are looking for. Here’s a link to the article.

  17. Hi Dave,

    Thanks for the post. I could use some help. I’m using Project 2013 and everything works fine except none of my summary task show the indicator. Any help would be appreciated.

  18. Hi Dave,

    Follow up on my previous question. All my summary tasks show a value of “0” so the indicator display what it is supposed to but any idea why I’m getting a value of ‘0″.

    Using the standard project field ‘Status Indicator” my summary task are showing the proper status.

  19. Hi Dave,

    It looks I figured it out. Here’s what I did in case someone run into the same problem.

    1- Had to setup Graphical Indicators for Summary Row like for Nosummary rows
    2- Had to click on Use Formula in the section “Calculation for task and group summary rows”

    Thanks again for your post

  20. Hi Eric,

    I don’t have Project 2013, but as noted, Project 2007 and 2010 the summary rows don’t calculate % complete reliably enough to use for this purpose. Have you tested a full range of scenarios, and found that it’s calculating reliably? If so, it’s one more reason to upgrade for a lot of us.

  21. Hi Dave,

    I still calculate it like 2007/2010. So the summary task do not reflect proper status. What I did is update the formula so:

    5 = Future Task
    4 = Complete
    3 = On Track
    2 = At Risk/Late
    1 = Past Due

    And in “Calculation for task and group summary rows” I selected “Rollup” and Minimum ( I don’t remember is this was available prior to 2013). This looks like the value for a summary task is equal the it’s subtask that has the minimal value. If at least one task in Past Due, the summary task will be listed as Past Due. Not perfect but accurate for me.

    Below is the updated formula in case someone desire to use it:

    IIf([Scheduled Duration]=0,(IIf([% Complete]=100,4,IIf([Scheduled Finish]<Now()+7 And [% Complete]=80,2,IIf([Scheduled Finish]<Now()+7 And [% Complete]=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),3,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),5,2),1)))))

    I also change the Graphical Indicators criteria to “Equals” to make it simple for me and not have to reorder them.

  22. Hi Eric and Dave,
    I tried to set up my project just like Eric’s and when I add the formula it gives me a syntax error but it does not highlight the problem. I am using Microsoft Project 2010.

  23. Hi Mark,

    I tried Eric’s formula, and it has two more left parens than right parens. It looks like part of the formula was omitted. In order to get it to work properly, you might want to start over with my original formula and modify it to meet your needs. Go to my Archive section, click on the MS Project Tricks page, and read the post on Crafting Your Won Formulas for Custom Fields in MS Project for some hints on how to DIY.

  24. That is the approach I took. I started over with your formula and I have it working the way I want now.

  25. Hi Dave!

    Thanks for sharing this awesome information.

    Just leaving my 2 cents here. For those using MS Project, having their currency configuration (Windows) using “,” it is recommendable, changing all “,” to “;”.

    Thank you again.

  26. Hey Dave,

    The formula has worked great for me! One note however: how come some summary tasks have a value of 0 (which is what I want), and how come some summary tasks have a value of 4 (which is signifying Late). I am using your exact formula that you used in this page:

    IIf([Scheduled Duration]=0,(IIf([% Complete]=100,5,IIf([Scheduled Finish]<Now()+7 And [% Complete]=80,3,IIf([Scheduled Finish]<Now()+7 And [% Complete]=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))))

    Any help would be much appreciated! I want all of the summary tasks to be 0! Thanks!

  27. Hi Johnson,

    Project has some differences in the way it computes percent complete in summary rows that make this approach ineffective. Consequently, I recommend you don’t display the results for summary rows.

  28. Hi Dave.

    Thanks for all the great info.

    I have a column that im using in a similar way, to flag up that something is due to happen and that we should take action.

    Im calculating from the total slack field.

    [Total Slack]/480

    The using the outcome of that to give me me a red amber green outcome.

    Id like to add an exception to this to say is the task 100% complete show blue.

    Our tasks sometimes complete before their marked duration. so the duration may have 8 days remaining or sometimes 20 yet we will mark the task as 100% complete.

    thanks in advance

  29. Hi Jason,

    Use this function to test if the task is complete:

    IIf([% Complete]=100, True_Action, False_Action)

    True_Action should display Blue, and False_Action should include whatever you are doing now.

    If this isn’t clear, Email me the formula and I’ll take a look at it. davegordon@practicingitpm.com

  30. How do I explain the following formula to a colleague:

    IIf([Scheduled Duration]=0,(IIf([% Complete]=100,5,IIf([Scheduled Finish]<Now()+5 And [% Complete]=75,3,IIf([Scheduled Finish]<Now()+5 And [% Complete]=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))))

    My intention is to say under 5 days less than 75% = very risky
    Over 5 days over 75% moderate risk
    then others are the same as you defined earlier.

  31. I need a graphical indicator to show when:

     

    Complete today

    Start date < 7 days in the future

    I see how I may be able to do that by modifying the finish date logic but I can't seem to get it to work. Is there a trick to the less than 7 days in the future part but I can't see?

  32. (Please delete my previous post – some text was lost)

    Thanks for the formula – I was struggling with the yellow vs green for off track vs on track tasks. I added in another red criteria that I have found helpful – turn red if today’s date is past the start date and the % complete is zero:

    IIf([Scheduled Start]-[Current Date]<0 And [% Complete]=0,"not started",

    My client also want's a good view of tasks 30-60-90 day windows in the future:

    IIf([Scheduled Start]-[Current Date]90,”future”,IIf([Scheduled Start]-[Current Date]<0,"null",IIf([Scheduled Start]-[Current Date]<30,"thirty",IIf([Scheduled Start]-[Current Date]<60,"sixty",IIf([Scheduled Start]-[Current Date]<90,"ninety"))))))

    Green is set to clear
    Null is set to clear
    Thirty is set to red plus
    Sixty is set to yellow plus
    Thirty is set to green plus
    Future is set to black plus

    Thanks again!

  33. Hi Dave,

    I’m trying to report in MS Project 2013 the % of late tasks but I’m failing. Any suggestions would be grateful?

    Thanks,

    Rob

  34. I would like to update what I posted earlier. I am just getting into this and I am still using the original formula. What I really am looking for is in fact the Clear Indicator. If it’s not scheduled to start yet, return Clear.

  35. Hi Dave

    Thanks a lot for the tips, they are really helpful
    One small favor: Can you please let me know how I can use the same formula even for the main tasks as well because your formula give me the status indicator for the subtasks but leaves it as blank for the main taks. I would like to have the main task also indicated as well as the overall project status.
    Please advise and thanks a lot for all your help in advance

    Regards
    Prasanth

  36. Hi Prasanth,

    MS Project doesn’t calculate % Complete for summary tasks the same way it does for detail tasks, so there is no reliable way to make the same status calculation work at that level.

  37. I would like to show a red light for tasks with negative Total Float. When using the beginning formula in this thread, tasks with negative Total Slack can be “green” if they are pushed out when a predecessor has been rescheduled. Of course tasks with negative Total Float need to be rescheduled, but as long as they are negative they should have a red light.

  38. Hi Dave,

    Thank you so much for this! I am new to Microsoft Project and relatively new to Project Management. This is exactly what my CFO asked for. Would you be able to tell me of a formula that only has 3 colors? I have a smaller project that the client just wants to see red, yellow and green to indicate green is on time, red is late and yellow has started on time. I’m not sure how to do this since I just copied and pasted your formula for all my other projects.

  39. Hi Jenna,

    If you only care about tasks in progress showing green, yellow, and red, you can point the other numerical values to a clear bubble.

  40. Hi Dave,

    thanks for the detailed tut and code. I would like to add some tip for the people coming from countries other than USA who changed their local settings – pay attention on the symbol for List separator. If it is “;” (as it is in my case), you need to use this sign instead of “,” in the formula – otherwise, you will get error notification (as I did, and spent some time to figure out why).
    Hope this will help.
    Regards,
    Mira

Comments are closed.