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 chartBy 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 chartPart 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 stylesIf 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.)
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!