On a challenge from a colleague (never tell me I can't) I wrote the two macros that conclude this post. They generate and manage status indicator bubbles for every line of your Project schedule, applying special rules to roll-up lines and reporting as of any user-specified date and time.

We found that these graphical status bubbles helped less technical folks keep their eyes from crossing. And we were surprised to find another value in them: team members ran as-if reports for the next weekly status report, to find out where they needed to focus in order to stave off the yellows and the reds. This engagement with the schedule left them with a better understanding of their work and its relation to the rest of the project. And graphical indicators just look so cool.

So what follows is the technical memo that describes and includes the two macros I built. Always be careful running macros you didn't build yourself, especially on your biggest, most important schedule. Oh, and after using these status symbols you are still going to need that Porsche.

Purpose

This memo describes MS Project (2007 and 2010) infrastructure that provides graphical indicators at both the task and summary task levels (see a later blog, from October 2, 2012, for the Project 2003 version). Status is measured based upon ‘% Work Complete', not ‘ %Complete'. In addition, the following variables are assumed throughout this memo, and may be readily adjusted by the user: ‘Future work, starting soon' is defined as starting within 7 days;‘Behind pace', or Yellow, is defined as less than 95% of the expected % Work Complete. Yellow is subject to unexpected values for tasks with long duration and unbalanced distribution of work.

Status is measured based upon ‘% Work Complete', not ‘ % Complete'. In addition, the following variables are assumed throughout this memo, and may be readily adjusted by the user: ‘Future work, starting soon' is defined as starting within 7 days; ‘Behind pace', or Yellow, is defined as less than 95% of the expected % Work Complete. Yellow is subject to unexpected values for tasks with long duration and unbalanced distribution of work.

Status options

Status description Code Indicator

Complete 0 blue bubble

Future work 1 white bubble

Future work, starting soon 2 white clock face

Green (on track) 3 green bubble

Yellow (behind pace) 4 yellow bubble

Red (late start or finish) 5 red bubble

Parent status rules

Summary tasks can generally take on the maximum status from their subordinate tasks. If a single task is red, for instance, the parent becomes red. Only if all children are complete will the parent show as complete.

The only exception is when children are all at values lower than 3 (Complete, Future, or Future Soon). In that case, if any task is complete the summary level should be green, because the task group is in process.

These rules, stated formally:

Maximum child

Status code

Parent status result

Meaning

Any child task

is complete

No child task

is complete

0

0

Every child task must be complete

1

3

1

If any work is complete, and nothing is YELLOW or RED, then parent is GREEN

2

3

2

3

3

Nothing is YELLOW or RED

4

4

Any single YELLOW makes parent YELLOW

5

5

Any single RED makes parent RED

Required Project infrastructure

Macro: status_calc

This macro sets the Project Status Date to a user-defined value, and uses custom number fields Number18, Number19 and Number20. Number20 is renamed ‘As of: [Status Date]', and displays the graphical indicators shown above. After running status_calc, the status formulas are active and will continue to be refreshed as changes are made to the schedule.

While active, the status formulas significantly reduce Project performance. For this reason, and to provide a clean snapshot, we recommend a second macro, to freeze the formulas and status as of the time you ran it. You can rerun the calc macro at any time. You may need to hide the previous status column to eliminate duplicate instances of the same column.

Macro: status_freeze

When the user is done performing any what-if checks, performance levels can return to normal by running status_freeze. This macro removes the formulas from Number18 and Number19, and hard-codes the existing status values in Number20.

Notes for use

Each time you run status calc it will erase your previous status column and reuse it for the current instance. This macro is not intended to generate a history of status at distinct times.

We recommend you save these to your general Macro workbook, not to an individual project, and add them to the Project toolbar, to make them readily available to all projects.

The two macros follow the page break.

******************************************

‘status_calc' macro:

Sub status_calc()

CustomFieldRename FieldID:=pjCustomTaskNumber20, NewName:=("Stale")

Dim OrigDate As Variant, NewDate As Variant, NewDateStr As String, DateOffset As Variant
Dim newTime As Variant

OrigDate = StatusDate
TryAgain:
NewDateStr = InputBox("Enter Desired Status Date and Time in the format mm/dd/yyyy 11:55 PM", "Status Date", OrigDate)
If NewDateStr = "" Then
End
End If
If VBA.InStr(1, NewDateStr, ":") = 0 Or _
VBA.Len(NewDateStr) < 15="">
MsgBox ("Enter Date and Time in format mm/dd/yyyy hh:mm AM")
GoTo TryAgain
End If

NewDate = DateValue(NewDateStr)
newTime = TimeValue(NewDateStr)
NewstatDate = NewDate + newTime

ActiveProject.StatusDate = NewstatDate

' Macro status_calc
' Macro Recorded 3/7/11 10:41 AM by fitets.
TableEdit Name:="Entry", TaskTable:=True, NewName:="", FieldName:="", NewFieldName:="Number18", Title:="", Width:=10, Align:=2, ShowInMenu:=True, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, ColumnPosition:=2, AlignTitle:=1
TableApply Name:="Entry"
SelectTaskColumn Column:="% Work Complete"
TableEdit Name:="Entry", TaskTable:=True, NewName:="", FieldName:="", NewFieldName:="Number19", Title:="", Width:=10, Align:=2, ShowInMenu:=True, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, ColumnPosition:=3, AlignTitle:=1
TableApply Name:="Entry"
SelectTaskColumn Column:="% Work Complete"
TableEdit Name:="Entry", TaskTable:=True, NewName:="", FieldName:="", NewFieldName:="Number20", Title:="", Width:=10, Align:=2, ShowInMenu:=True, LockFirstColumn:=True, DateFormat:=255, RowHeight:=1, ColumnPosition:=4, AlignTitle:=1
TableApply Name:="Entry"
SelectTaskColumn Column:="Number18"
CustomFieldSetFormula FieldID:=pjCustomTaskNumber18, Formula:="IIf([% Work Complete]=100,0,IIf(([Start]=[Finish]) And ([Status Date]<[start]),iif([start]-[status>[start]),iif([start]-[status><7,2,1),iif([finish]=[status date],iif([%="" work="" complete]="">7,2,1),iif([finish]=[status><>[Finish]) And ([% Work Complete]=0) And ([Start]>[Status Date]),IIf(([Start]-[Status Date])<><>[Finish]) And ([% Work Complete]>0) And ([Start]>[Status Date]),3,IIf((([Start]<>[Finish]) And ([% Work Complete]=0) And ([Start]<[status date])="" and="" ([status="">[status><><>[Finish]) And ([% Work Complete]<100) and="">100)><[status date])),5,iif(([status="" date]="">[Start]) And ([Status Date]<[finish]) and="" ([%="" work="" complete]="">0),IIf(((([Status Date]-[Start])/([Finish]-[Start]))*95)-[% Work Complete]>0,4,3)))))))))"
CustomFieldPropertiesEx FieldID:=pjCustomTaskNumber18, Attribute:=pjFieldAttributeFormula, SummaryCalc:=pjCalcRollupMax, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
SelectTaskColumn Column:="Number19"
CustomFieldSetFormula FieldID:=pjCustomTaskNumber19, Formula:="switch( [% Work Complete] = 100,1, [% Work Complete] <>
CustomFieldPropertiesEx FieldID:=pjCustomTaskNumber19, Attribute:=pjFieldAttributeFormula, SummaryCalc:=pjCalcRollupAverage, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
SelectTaskColumn Column:="Number20"
CustomFieldSetFormula FieldID:=pjCustomTaskNumber20, Formula:="switch(([Number18]=0),0,([Number18]=1) And ([Number19]=0),1,([Number18]=1) And ([Number19]>0),3,([Number18]=2) And ([Number19]=0),2,([Number18]=2) And ([Number19]>0),3,([Number18]>2),[Number18])"
CustomFieldIndicators FieldID:=pjCustomTaskNumber20, SummaryInheritsNonsummary:=True, ProjectInheritsSummary:=False, ShowToolTips:=True
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber20, Test:=pjCompareEquals, Value:="0.00", IndicatorID:=pjIndicatorSphereBlue
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber20, Test:=pjCompareEquals, Value:="1.00", IndicatorID:=pjIndicatorSphereWhite
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber20, Test:=pjCompareEquals, Value:="2.00", IndicatorID:=pjIndicatorClock
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber20, Test:=pjCompareEquals, Value:="3.00", IndicatorID:=pjIndicatorSphereLime
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber20, Test:=pjCompareEquals, Value:="4.00", IndicatorID:=pjIndicatorSphereYellow
CustomFieldIndicatorAdd FieldID:=pjCustomTaskNumber20, Test:=pjCompareEquals, Value:="5.00", IndicatorID:=pjIndicatorSphereRed
CustomFieldPropertiesEx FieldID:=pjCustomTaskNumber20, Attribute:=pjFieldAttributeFormula, SummaryCalc:=pjCalcFormula, GraphicalIndicators:=True, AutomaticallyRolldownToAssn:=False
SelectTaskColumn Column:="Number19"
SelectTaskColumn Column:="Number20"
CustomFieldRename FieldID:=pjCustomTaskNumber20, NewName:=("As of: " & ActiveProject.StatusDate)

SelectTaskField Row:=0, Column:="Name"
SelectTaskColumn Column:="Number19"
ColumnDelete
SelectTaskColumn Column:="Number18"
ColumnDelete
End Sub

‘status_freeze' macro:

Sub status_freeze()
' Macro status_freeze
' Macro Recorded 3/7/11 11:02 AM by fitets.
CustomFieldSetFormula FieldID:=pjCustomTaskNumber18, Formula:=""
CustomFieldPropertiesEx FieldID:=pjCustomTaskNumber18, Attribute:=pjFieldAttributeNone, SummaryCalc:=pjCalcRollupMax, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
CustomFieldSetFormula FieldID:=pjCustomTaskNumber19, Formula:=""
CustomFieldPropertiesEx FieldID:=pjCustomTaskNumber19, Attribute:=pjFieldAttributeNone, SummaryCalc:=pjCalcRollupAverage, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
CustomFieldSetFormula FieldID:=pjCustomTaskNumber20, Formula:=""
CustomFieldPropertiesEx FieldID:=pjCustomTaskNumber20, Attribute:=pjFieldAttributeNone, SummaryCalc:=pjCalcNone, GraphicalIndicators:=True, AutomaticallyRolldownToAssn:=False
' SelectTaskColumn Column:="Number19"
' ColumnDelete
' SelectTaskColumn Column:="Number18"
' ColumnDelete
End Sub

[finish])>[status>