Project Management

Create a traffic light Gantt chart in Microsoft Project 2007

One way to draw attention to problem areas in a project schedule is to set up a traffic light Gantt chart. Learn how in this Microsoft Project 2007 tutorial.
In my previous Microsoft Project tutorial, I describe how to develop a portfolio dashboard to communicate a summary level status for each project in your portfolio or program (Figure A). One TechRepublic member requested that I write a tutorial on how to apply the same concept to their individual tasks in their project schedules. Figure A

Program Gantt chart
By creating a few custom formulas using Microsoft Project's Finish Variance field and Bar Chart styles, I was able to create a graphical Gantt chart that would format itself based on the project performance against the project baseline (Figure B). Figure B

Traffic light Gantt chart (Click the image to enlarge.)

By using these formulas and bar styles, a project manager can quickly scan the Gantt chart for all the at-risk tasks in the project schedule and determine if any of the tasks will be forecasting late or need to be replanned. This view is particularly useful to determine where the project team needs to focus and identify the tasks that are at immediate risk for running behind.

In project tracking, we often look at the late tasks but don't assess the long-term impact to project delays to succeeding tasks in the project schedule. This bar style provides a graphical "traffic light" view of each task in the schedule. Microsoft Project's Finish Variance is the key field that drives the Gantt chart bar style. The Finish Variance field calculates the number of days late a task will finish based on the project baseline.

Finish Variance = Finish - Baseline Finish

Using this formula, I established the following rules to color code the bars:

  • If the Finish Variance is less than two days, the task can still be considered Green. In actual practice, tasks can run late and, as long as it is within a containable range that doesn't impact the completion date, the task could still be considered Green.
  • If the Finish Variance is greater than two days and less than or equal to five days, the task is considered at risk or Yellow.
  • If the Finish Variance is greater than five days, the task is Red.

Feel free to change these rules based on your organization or project guidelines. Remember, we're not setting up rules to report the entire project as Red, Yellow, or Green -- those subjective assessments require more data than just a schedule review. This tutorial establishes tolerance ranges that color code the Gantt chart for quick identification and further inspection.

Since the Gantt chart evaluates the forecasted tasks based on the Finish and the Baseline Finish columns, it is likely that forecasted tasks will appear in a Red status. Even if you have tasks that are not planned to start in a given time period, the tasks could show up late based on their forecasted finish dates. This occurs because Microsoft Project is calculating a future finish date for each task even if it is different from the Baseline Finish date. Project managers should appreciate this feature, as it draws attention to the tasks that need immediate investigation.

Important notes

Before you customize the Gantt chart, please note that:

  • A Baselined project schedule is required to properly format the Gantt bars. If you are building the project schedule using these bar styles, all the tasks will appear Green.
  • This tutorial is developed for Microsoft Project 2007; if you are developing it in an earlier version of Microsoft Project, I cannot guarantee the same results.

How to create the traffic light Gantt chart

Part A: Customize Flag1, Flag2, and Flag3 fields 1. Start with the Gantt Chart View using the Entry Table.

2. Insert Flag1, Flag2, and Flag 3 into the Gantt Chart Table.

3. Right-click Flag1 and select Customize Fields.

4. In the Custom Fields dialog box under the Custom Attributes heading, select the Formula radio button.

5. Click the Formula button (Figure C). Figure C

Custom Fields dialog box

6. Enter the following formula and click OK

IIf([% Complete]<>100,IIf([Finish Variance]/[Minutes Per Day]<=2,Yes,No),No)

This formula will evaluate all incomplete tasks and determine if the variance is less than or equal to two days (Figure D). It returns a Yes value if the equation is true and a No value if the equation is false. Figure D

Formula for Flag 1

7. In the Custom Fields dialog box (Figure C), click the Flag2 field.

8. Click the Formula button.

9. Enter the following formula and click OK

IIf([% Complete]<>100,IIf([Finish Variance]/[Minutes Per Day]>2,IIf([Finish Variance]/[Minutes Per Day]<=5,Yes,No),No),No)

10. In the Custom Fields dialog box (Figure C), click the Flag3 field.

11. Click on the Formula button.

12. Enter the following formula and click OK

IIf([% Complete]<>100,IIf([Finish Variance]/[Minutes Per Day]>5,Yes,No),No)

13. Click OK to close the Custom Fields dialog box.

Part B: Create the Red, Yellow, and Green bar styles

1. In the Gantt Chart View, select Format | Bar Styles.

2. Insert three rows and name them Red, Yellow, and Green.

3. On the Red row, click the Color drop-down menu and change it to Red (Figure E).

4. Change the Show For ... Tasks field to the Flag1 field.

5. Repeat steps 3 and 4 for the Yellow and Green rows and assign Flag2 and Flag3 respectively.

6. Click OK.

Figure E

Bar styles
If you've followed the instructions and are using a Baselined project schedule, your Gantt chart should light up like a Christmas tree (Figure F). Hopefully, you aren't seeing all red! Figure F

The final product (Click the image to enlarge.)

Summary

There are a lot of ways to track your project schedule, and Microsoft provides multiple views into project data. The traffic light Gantt chart offers a forecasted view into tasks that are late or are at risk of running behind schedule. You could obtain the same information by following a tracking Gantt chart or monitoring the critical path; however, this approach adds a little more color and draws attention to the problem areas in your project schedule.

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.

4 comments
CJ_winter
CJ_winter

Sorry I mean - there is no variance between Finish, and Baseline finish. So why is it coming up Red?

CJ_winter
CJ_winter

I did this on a newly baselined project and it seems the green and red are backwards. There is no variance between "finish" and "finish variance" so it was strange it was coming up all red.

Any reason why this would happen?


joebailey77
joebailey77

This is fanstatic, how can I implement this with 11 projects...??? Joe

kbanh
kbanh

Great article. This article somewhat aligns with an effort I have assigned. I need to produce a dashboard/report of in-flight projects (by phase) in a calendar format. I thought about using the Gantt chart in MS Project, but it's too manually intensive. Can I setup a template which will then color-code the various project phases and then roll them up to the project level? I realize I need to show the various phases by project, but when I generate this "calendar" I need to summarize it at the project level with the tasks (phases) rolled up. Again, lots of good info in the article.

Editor's Picks