MS Project: Tasks Behind Schedule

The new year has been extremely chaotic with work and shoveling (I live in the DC metro area), which has been detrimental to my blogging. Leave it to a random reader to spur my creative bone – albeit to my technical side.

A commenter asked in response to my Visual Basic capsule on writing code to make Gantt bar colors match resources, a question about formatting based on the tasks. Here is the comment:

The code works and I accept that there is no non VBA method of achieving the result above.

But I know that you can change the colour of bars for example to Blue or Red depending on whether the car is critical or not via Format bar styles. Can you specify a colour for the bar if the task is behind schedule without Code?

I find it fascinating that search engines allow for readers on the other side of the Atlantic to find tiny, inconsistent blogs like my own. The question shook me out of my all-important holiday of Presidents’ Day doldrums and back into geek mode.

The quick answer to the question is ‘yes’, but you will likely have to think like a developer in order to get the result that you want. The good news is that you won’t need VBA to do it.

The primary place the action will take place is in the Format Bar Styles dialog (select Format | Bar Styles). The default list shows how MS Project applies bar styles for the open project are shown. The list shows the types and order in which the styles are applied. This means that if a task falls into two or more row criteria, the row closest to the bottom of the list will be the one that is applied.

For example, I have taken a screen shot of two custom row criteria that I entered to get close to what I think of as “behind schedule” – Not Started and Not Finished. I have put Not Finished before Not Started, since all tasks must start before they can finish. If I had reversed the order, putting Not Started first, that color (or colour) would never appear.If you aren’t confused yet about putting things in the proper order, this little experiment highlights a problem with using just the Bar Styles dialog to accomplish this task – the requirements. John wants to know how to show tasks that are “behind schedule”. Showing tasks that have not started or finished do not speak to the state of the task. To do that, we need to base our criteria on either the baseline or the current (or status) date.

Managers who are seeking to show tasks “behind schedule” using baseline are looking for historical reports. Managers who are seeking to show tasks “behind schedule” using current date or status date are seeking to refine the format of the schedule to highlight where the problem areas lie. Both are valuable goals, and both can be accomplished with using VBA code. The former is the easier of the two (and does not require custom fields), so I will deal with that first.

Baselines. MS Project (2003) offers 21 different default categories (such as Critical), and each can be combined with a comma – e.g., see the settings for “External Milestone”. The comma represents an “and” for development, not an “or”, meaning all criteria listed in the row must be satisfied to affect the bar style. Since I don’t have any other data to go on than “behind schedule”, I am going to use the following base formula to determine tasks that are “behind schedule”:

BS = Started Late (Tasks with a scheduled start date later than the baseline start date) AND Finished Late (Tasks with a scheduled finish date later than the baseline finish date)

I am not using an “OR” separator (meaning two separate lines) because for my purposes, it does not matter if a task starts late but still finishes on time. But, we don’t want to rule out tasks that Started on Time, so we also need to create a second line for just Finishing Late alone. So, the final base formula we will use is:

BS = (Started Late AND Finished Late) OR Finished Late.

Translating that to the project dialog, it will look like this:
Now for the trickier part…

Current Date. There are no default categories that point to the current date or status date, but there are 20 custom Flag fields we can use. In this case, I’m going to use Flag1 and make the color Lime green, so I can see the difference in my project. (For the purposes of this experiment, I am going to assume it is more important – i.e., put lower in the bar list – to highlight tasks that are behind as of today versus behind in versus the baseline.)Click “OK” to close the dialog, and then select Tools | Customize | Fields. In the Custom Fields dialog, use the right drop-down to select “Flag” for Task Fields. Rename Flag1 as (in this case) “Behind Schedule C” for traceability* and then click the Formula button.As with the Baselines, now we need to start with a base formula for behind schedule. I will use the following:

BS = ([% Complete]< 100 And [Finish]<[Current Date]) OR [Start]<[Current Date] And [% Complete]=0)

In other words, if the Start date is after today and it hasn’t started, it is behind schedule. If the Finish date is after today and the task isn’t complete, it is also considered behind schedule.

We have another slight caveat with Flag, and that is that Flag fields give a strictly “Yes” or “No” or blank output. Using the Function button and select Function -> General -> IIF to paste a generic “If, then, else” statement into the text box.

The end result looks like this (cut/paste into dialog):

IIf(([% Complete]<100 And [Finish]<[Current Date]) Or ([Start]<[Current Date] And [% Complete]=0) Or ([% Complete]<100 And [Finish]<=[Current Date]),"Yes","No")

Graphical Indicators. An additional feature that I use is to create a Graphical Indicator, so you can sort through your fields quickly and find trouble areas. (I use a text field, for multiple criteria, such as “Late”, “At Risk”, “Upcoming”, and “Complete”, but that is another blog entry.). To get the field set up, do the following:
Testing/Viewing.Here’s a screen shot of various scenarios with the indicators and gantt bar colors in place:
Replicating: The MS Project Organizer does not have a section for Bar Styles, so you cannot upload the setting to your Global MPT file. Fields (such as Flag1) can be easily replicated. I recommend modifying your organization’s project templates and including a short ‘how to’ procedure in your organization’s PMO tips guide. It only takes a minute to set up.

Overall. MS Project can really make managing projects easy for you, if you put a little work into it.

* I highly recommend documenting all customizations in MS Project that become standard. Over the years, you can accumulate a lot of customized baggage that no one remembers what is used for, but because no one wants to break anything, it becomes part of the legacy application.

MS Project VBA: Coloring Gantt to match Resource

I had copious amounts of spare time working as a technical writer 14 years ago, but one of the things I'm proud of is that for as much time as I wasted, I also invested in my future. I knew the IT guy at the workplace, and convinced him to install a copy of Visual Basic 5 on my computer, and then picked one of those "learn in 21 days" books. Although I have never really had use for Visual Basic in my travels, the Visual Basic for Applications knowledge -- writing macros for MS Project and MS Excel, for instance, has been invaluable in every job I've had since.

Coding isn't for everyone, but I find it interesting and a challenge. It appeals to the geek in me. Had I had more intererst at an earlier age (or VB existed back then -- I have always thought if anything I was born too soon), I would have probably become a master hacker by this point. I love logic and coding problems, and it's amazing to me how few people in business have the skills to do these things.

As an example of geeking out, on Linked In I received a notice of a discussion about how to automatically have the Gantt chart bar (in MS Project) change color depending on the resource. A scheduler had posted the question (posed by the client) and then asked if there was an easy way to do it.

The short answer is no. The slightly longer answer is to do it with VBA coding or a lengthly formula and field links. But, I couldn't turn away from the computer, even though my bathroom tub cleaning was really on my agenda for this morning, so I spent about a half-hour figuring out how I would do it. My guess is that this will turn out to be a requirements-changing product -- what I mean by that is once the client sees the rainbow of colors on the Gantt, he'll rethink his request. (Business "idea people" often need this reality check.)

Of course, the sad (and my girlfriend says "really attractive") thing is that this whole little coding experience was fun. Really fun. Embrace the geek.

Here's the code I wrote below. To work, cut and paste into a module in MS Project. Not that you'll need it.

Sub BarColorByResource()

'This sub will work only if the resource name matches correctly. The units removal section is
'not needed if every resource is allocated at 100%. If no resources match, default bar color is reset.

'Color constants (use on Middle color) ------------------------------------------
'pjColorAutomatic pjNavy pjAqua pjOlive pjBlack pjPurple pjBlue pjRed
'pjFuchsia pjSilver pjGray pjTeal pjGreen pjYellow pjLime pjWhite pjMaroon

Dim Tsk As Task
Dim sResource As String

'Scroll through all data rows in project
For Each Tsk In ActiveProject.Tasks

'Ignore tasks where there is no data (blank lines)
If Not Tsk Is Nothing Then

'Remove units brackets
sResource = Remove_Brackets(Tsk.ResourceNames)

Select Case sResource

Case "Matthew Jones" 'Matthew Jones

EditGoTo ID:=Tsk
SelectRow
GanttBarFormat MiddleColor:=pjAqua


Case "John Smith"

EditGoTo ID:=Tsk
SelectRow
GanttBarFormat MiddleColor:=pjNavy

'etc...

Case Else

EditGoTo ID:=Tsk
SelectRow
GanttBarFormat MiddleColor:=pjBlue 'default color restore


End Select

End If

Next Tsk

End Sub

Function Remove_Brackets(sTmp)

'Function will remove multiple instances of bracketed [] text in a string

iLen = Len(sTmp)

For i = 1 To iLen

sTmpChar = Mid(sTmp, i, 1)
If sTmpChar = "[" Then iLeft = i - 1
If sTmpChar = "]" Then

iRight = i
sTmp = Left(sTmp, iLeft) + Right(sTmp, iLen - iRight)
i = 1
iLen = Len(sTmp)

End If

Next

Remove_Brackets = sTmp

End Function