Adding a Calculated Status Indicator to Tasks in 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.

One of the things I like about using MS Project is that it includes the ability to define calculated fields.  While there is an included Status field, it’s not exactly what I want.  I’ve recently started using a numeric field to calculate the status of tasks in the project plan, and display stop lights – Green, Yellow, and Red.  I’ve found it adds some immediacy to project status meetings.  Here’s what it does:

  • For zero duration tasks (milestones)
    • If % Complete = 100, return Green
    • 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 Green
    • 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.

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).  Then 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.

Then click on the Formula button, and past in the following:

IIf([Scheduled Duration]=0,(IIf([% Complete]=100,2,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 Or [% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish])))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4))))

Click 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 3.5 with Red, 2.5 with Yellow, 1.5 with Green, and .5 with Clear.

Then click OK.  Your project plan should look like this:

On September 30, Task 1 is Green because it’s complete.  Task 2 is Red because it’s past the finish date and less than 100% complete.  And Task 3 is Yellow because three days have passed, the task was scheduled for four days, but it’s only 50% complete.  If the task were 75% or greater, it would be green.  Note that “days” is defined using the project calendar, so it only uses working days.

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 MS Project 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. 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.

19 thoughts on “Adding a Calculated Status Indicator to Tasks in MS Project

  1. Dave, I like the addition of the “Clear” indicator. This set of indicators will keep you focused on the most pressing tasks.
    Keep up the great blog. Thanks .

  2. Glad you liked it, Matt. Take a look at this week’s post, which shows how to add a “current tasks” flag, so you can always have your team focused on work in progress and starting in the coming week.

  3. Hi Dave,

    The logic below works great for my individual tasks, but I would like to apply the same logic to to my milestones.

    f(([% Complete]=100 Or [% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish])))),2,(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),1,3),4)))

    I am trying to automate our PMO’s performance report and this would be extrememly helpful. Also, is there anyway for Project to autofill the milstone % complete status? Or is the best practice to enter the % complete from the summary task?

    Any help would be greatly appreciated!

    Thanks,
    Dustin

  4. Hi Dustin,

    Note that milestones have a zero Scheduled Duration. In my sample code, the first thing I do is determine whether the task has a zero duration; if not, then I perform the date math. Otherwise, I’d get a divide by zero error.

    If you want to report on progress as a percent complete, definitely use the summary tasks. Project calculates them for you, based on the subordinate task durations and percent complete. However, it won’t calculate milestones. Tell your project managers and administrators to enter % complete on milestone tasks as either 0% or 100%, since they’re essentially flags telling you that you’ve passed some point of interest.

    I hope this helps.

  5. Hi Dave, Thank you and this is great. I would appreciate your assisstance with the following:
    • For non-zero duration tasks
    o If % Complete = 100, return Green
    o If it’s not scheduled to start yet, return Clear
    o If it’s past the scheduled finish date, return Red
    o If % complete >= the prorated expected completion, return Green
    o If variance between % complete and the prorated expected completion >=80%, return Yellow
    o Otherwise, return Red.

    Regards,
    Siva.

  6. How can I show the indicators on Summary Tasks to capture status of Projects within my program plan for dashboard reporting?

  7. Shelby, the % Complete for summary tasks doesn’t work like it does for detail tasks, and there doesn’t appear to be a way to accurately calculate and report a “stop light” status for them.

  8. Hello Dave,

    First off, thanks so much for making this. I have one question however.. Is it possible to have the parent task to each individual task show a stop light marker. For example if a parent task has 3 tasks under it, two are green, and one is red the parent task would show red or if there were 3 tasks all of which were green the parent task would show green. Would this be possible?

    Thanks!

  9. Hi Jacob,

    As I replied to Shelby, the % Complete doesn’t work for the summary (parent) tasks the way you would expect it to. I’ve tried to get a meaningful result, but so far, no joy.

  10. Using the above formula, how can I add tasks that have % complete = 100 to show blue indicator? I want to distinguish completed task with blue while task on target green. Thanks for your help.

  11. Thank you SO MUCH for this! I have tried several other tutorials and none of them worked but yours worked right the first time.

  12. Hi David,

    This is excellent! However can you please assist me with adding an additional line in this formula that will highlight 100% complete tasks as seperate to tasks that are on track. For example, I want tasks that are complete to have a Tick Icon and those that are on track to have a green light.

    Really appreciated your help

    Thanks,
    Konrad

  13. My tasks with a start date that haven’t yet begun are showing RED. I copy/pasted the formula so I’m not sure why it’s not working. Any help?
    Thanks for a great tutorial!

  14. Hi Wendy,

    Under “Values to display,” click “Data” instead of graphical indicators. Tasks with a start date in the future should have a “1” and incomplete tasks with a finish date in the past should have a “4.” If the numbers are correct, you associated the numeric values with the wrong colors. If the numbers are wrong, it’s a formula problem. Let me know which one.

    Try the newer version of this tutorial, by clicking the link at the top of the page. It includes a downloadable Project file you can use as a starting point.

Comments are closed.