Project Management

Add the Late Indicator tool in Microsoft Project 2007

Learn how to build the Late Indicator tool so anyone viewing your project schedule can quickly determine if any tasks are behind schedule.

One challenge in reviewing project schedules on a weekly basis is quickly identifying late tasks. In previous tutorials, I showed how to build the Behind Schedule filter and how to use the Slipping Tasks filter. Here's another approach to identifying late tasks: using a graphical indicator. This is a useful way to review all the project tasks in one view and use a graphical indicator to show if a task is on or behind schedule. (This tutorial is specific to Microsoft Project 2007.)

Late Indicator

The Late Indicator (Figure A) is based on a simple calculation that looks at all incomplete tasks and compares the Baseline Finish Date to the Project Status Date. Like the Behind Schedule filter, the tool compares the project baseline against the weekly project status date. Note: If you don't manage the project schedule using a Baseline Plan, this graphical indicator will not work. (If you don't use a Baseline Plan to manage your projects, I hope this tutorial inspires you to do so.) Figure A

Late Indicator (Click the image to enlarge.)

Once a project baseline is set and you record your weekly project status date in Microsoft Project, the graphical indicator will quickly identify all the late tasks in your schedule as well as the tasks that are not behind schedule.

To build the Late Indicator, follow these steps:

1. Select Tools | Customize | Fields.

2. In the Type combo box, select the Number value.

3. Select an unused Number field (i.e., Number1, Number2, Number3).

4. Click the Rename button.

5. Enter Late Indicator in the Rename Field (Figure B). Figure B

Rename Field

6. Click the Formula button.

7. Enter this formula:

IIf([% Complete]<>100,DateDiff("d",[Baseline Finish],[Status Date]))

The formula (Figure C) examines all incomplete tasks and compares the Baseline Finish Date to the Project Status Date field. If a task is incomplete, the difference between the two dates will be reported. Figure C

Late Indicator Formula

8. Click OK.

9. In the Values to Display section, click the Graphical Indicators radio button.

10. Enter the following tests, values, and images from the drop-down boxes (Figure D). Figure D

Graphical Indicators

11. Click OK twice.

At this point, a custom field has been modified with a formula that displays a graphical indicator. The next step is to insert the Number field into your Gantt Chart view and set the Project Status date. In my example, I modified the Number2 field and, to add it to my current view, I selected Insert | Column and selected the Number2 (Late Indicator) field from the Field Name drop-down menu. The last step is to update the project status date.

12. Select Project | Project Information (Figure E).

13. Select the Project Status date from the drop-down calendar.

Figure E

Project Information

Once the Status Date is set, the graphical indicator will "light up" and identify the late tasks with the red bulb indicator. Remember that you'll need to update your project progress weekly and change the status date accordingly. As the project progresses, the indicators will change. When a task is completed, the indicator will disappear as the indicator looks only at incomplete tasks.

Summary

Microsoft Project can present an overwhelming amount of data with its different views and underlying data tables. I find it useful to include graphical indicators in a project schedule so anyone viewing it can quickly determine if there are tasks running behind schedule. It can also be used in your status reporting by applying the Milestones filter.

Go ahead and experiment with the different formulas and graphical indicators available in Microsoft Project. In a future tutorial, I'll share a few more useful formulas and graphical indicators that help improve status reporting.

Get weekly PM tips in your inbox TechRepublic's IT Project Management newsletter, delivered on Wednesday, offers tips to help keep project managers and their teams on track. Automatically sign up today!

About

Dr. Andrew Makar is an IT program manager and is the author of How To Use Microsoft Project and Project Management Interview Questions Made Easy. For more project management advice visit http://www.tacticalprojectmanagement.com.

14 comments
jeremy.steuhl
jeremy.steuhl

I've been able to get the indicator to work for the individual tasks, but would be good to see the indicator on the parent task. Any ideas to make that happen?

jordoc7
jordoc7

Hi, this is a good article especially the screen dumps. However I find when every thing is set up I cannot test sucessfully. It works if I simulate late tasks by changing the dates but changing the percentage complete does not work as expected. So for example if a task is 20% complete but the it has 9 days to run and the task is 10 days long, clearly the task is on schedule however if I change the task to 1% the task is at risk but the indicator doesn't go red....as percent complete is the main updated field surely this should control if a task is at risk?

wtlwill
wtlwill

how can i exclude summary tasks from showing the late indicator?

btuggle52
btuggle52

I had used this method previously and it is a great way to see problem areas quickly.

markke
markke

I received an error but after changing the double quotes to single quotes, it accepted the formula and seems to work. Corect Formula: IIf([% Complete]100,DateDiff('d',[Baseline Finish],[Status Date]))

dennisk68
dennisk68

I followed the directions and it works great (remember to change the " to ' in the date formula). I like how the indicator disappears when the task is complete. Unfortunately it doesn't disappear for summary tasks. Do you know how I can get it to disappear for summary tasks that are complete?

BrennanJ
BrennanJ

I receive the following error when I try to enter the formula: "The formula contains a syntax or contains a reference to an unrecognised field or function name. To return to the Formula dialog box and highlight the error, click OK." I am using MS Project 2003.

madhuri.jha
madhuri.jha

I tried following the steps in the article, but received error message saying 'formula contains syntax error or contains reference to an unrecognized field or function name' - "d" within the formula is highlighted

lello0752
lello0752

What does mean: remember to change the " to ' in the date formula ? Could you, please, write down the working formula ? Thanks a lot

n.tsokas
n.tsokas

I receive the following error when I try to enter the formula: "The formula contains a syntax or contains a reference to an unrecognised field or function name. To return to the Formula dialog box and highlight the error, click OK." I have saved the base line, but no luck. Ia have also changed " to '. I am using MS Project 2003.

mikesmccann
mikesmccann

If you are getting a syntax error with the "d" highlighted, change the " to a ' so that the final formula looks like this: IIf([% Complete]100,DateDiff('d',[Baseline Finish],[Status Date]))

tonyb
tonyb

hi tried your suggestion IIf([% Complete]100,DateDiff('d',[Baseline Finish],[Status Date])) but still get #error message.

n.tsokas
n.tsokas

I get an error and DateDiff is highlighted

tonyb
tonyb

Oops. You need to put a value on the baseline finish column. You can do that by: 1. Right click on the Task Header 2. click insert column 3. Look for Baseline finish on the Field name. 4. Specify baseline finish on each task. That should do the trick. Don?t forget to set the project status date in Project Information.