Conditional Formatting in Microsoft Project Gantt Charts
Part three of a three-part series of blogs

Formatting Gantt Charts in Microsoft Project is a time-consuming process. This blog series explains how to create task bars that format themselves as you change information in the table.

  1. Step 1 - creating a custom field in Microsoft Project
  2. Step 2 - creating a custom format in Microsoft Project
  3. Step 3 - creating a calculated custom field in Microsoft Project (this blog)

Posted by Andrew Gould on 01 June 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Project conditional formatting - creating a calculated custom field

So far in this series of articles we've seen how to use a custom field to control the colour of task bars in a Gantt Chart.  The final step is to link our custom field to the list of assigned resources so that the formatting happens automatically.

Creating a Calculated Custom Field

So far we've had to change the value in our custom field manually.  Now we're going to get the custom field to calculate automatically by looking for the word "Santa" in the list of resources assigned to each task.  To start doing this, from the menu select: Tools -> Customize -> Fields…

Customising a field
  1. Select Flag from the drop list.
  2. Select the field you previously customised.
  3. Click the Formula… button.

At this point you'll find yourself in the Formula dialog box - now we need to create a calculation that will return TRUE when the word "Santa" appears in the Resource Names field in the task table.  The final formula is shown below, followed by an explanation of how it works:

InStr( 1 , [Resource Names] , "Santa" ) <> 0
  • InStr() - This is the name of a function that looks for one piece of text inside another.  All functions have a set of round brackets to contain their arguments and commas to separate one argument from the next.
  • 1 - This tells the function which letter to begin searching at.  We're using 1 to make sure that we begin looking at the start of the text.
  • [Resource Names] - This is the name of the field that we're looking in.  Field names are enclosed in square brackets.
  • "Santa" - This is the specific piece of text we're looking for.  We have to use double quotes to indicate that it's literal text rather than a field name for instance.
  • <>0 - This sequence of symbols means "not equal to zero".  When the InStr function doesn't find the word "Santa" it will return zero as the answer.  We only want to find the tasks where we do find Santa so we use this test to eliminate the tasks that Santa has not been assigned to.

You can type this formula directly into the dialog box, or use the Field and Function drop down lists to build the calculation step-by-step.

Creating a formula

When you think you've got it right, click OK to enter the calculation.

You will probably see a warning message appear when you click OK.  This is simply to tell you that any existing values in the field will be replaced with the calculated values, so click OK again until you get back to the Gantt Chart view.

A pointless warning message

Just click OK if you see this message.

When you get back to the Gantt Chart view, you should see that any of the tasks with Santa assigned to them are automatically coloured in red.

The final result

You can try changing the resource assignments to make sure that the formatting changes automatically.

To tidy up the table you can even hide the custom field - it will continue merrily calculating in the background. 

The criteria for your custom format could be based on any information in the table, all you need is a Flag field with a formula that returns TRUE or FALSE.  For instance you could highlight task bars where the Total Cost is higher than the Baseline Cost, or where the Actual Finish Date is later than the Baseline Finish Date, etc.

  1. Step 1 - creating a custom field in Microsoft Project
  2. Step 2 - creating a custom format in Microsoft Project
  3. Step 3 - creating a calculated custom field in Microsoft Project (this blog)
This blog has 0 threads Add post