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.
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 .
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.
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
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.
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.
How can I show the indicators on Summary Tasks to capture status of Projects within my program plan for dashboard reporting?
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.