Color-Coding by a Custom Status field in Microsoft Project

Many of you already use OnePager’s conditional formatting to automatically assign colors to your charts based on Microsoft Project’s Status field. But what if you need status calculated differently than how Microsoft Project does it out of the box?

In this article, I’ll show you how to create your own custom status field in Microsoft Project, and then bring that into OnePager to drive the color-coding of your timeline.

Let’s start with a simple plan that has tasks in various statuses. Microsoft Project has calculated the status for each field, which generally looks pretty good:

However, I don’t like the concept of “Future Task” because it really doesn’t tell me how far into the future a given task is going to start. If a task starts next week, I should probably start paying attention, but if there’s a month or more to go, I could probably focus my attention elsewhere.

We’ll write a custom status formula that largely follows the standard Status field, but when it comes to future tasks, we’ll make a distinction between those that are starting within the next month and those that are farther out. In the Text30 field, I will write a formula like this:

Switch(
	[% Complete] = 100, "Complete",

	([Start]-Now()) <31 AND ([Start]- Now()) >= 0, "Soon",

	([Start]-Now())>=31 AND ([Start]- Now()) >= 0, "Future",

	(ProjDateDiff([Start], Now()) / ProjDateDiff([Start], [Finish])) * 
        100 > [% Complete], "Late",

	(ProjDateDiff([Start], Now()) / ProjDateDiff([Start], [Finish])) * 
        100 <= [% Complete], "On Track"
)

Unlike the standard Status field, we have five conditions here instead of four, and that’s what helps identify near-term and longer-term task start dates. Everything else works the same way, more or less. Once I apply the formula, Text30 is populated with my new, more precise status values:

Notice that the last two tasks, which were just identified as a “Future Task” in the original field, now report either a “Soon” or “Future” start.

With this more accurate status set up in Microsoft Project, we can create our OnePager chart. Here’s a look at the conditional formatting rules that we can use to identify all five statuses with a unique color:

Once we apply these rules to the OnePager chart, we get a color-coded timeline based on the custom status field that we set up in Microsoft Project:

This entry was posted in Microsoft Project Formulas, Microsoft Project Tips, OnePager Pro Tips, Plan Communications, Project Reporting, Project Visualization by Safford. Bookmark the permalink.

About Safford

Safford is a versatile technology professional with a solid history of empowering emerging growth companies in a broad array of industries. His employment history includes energy industry consulting at Quorum Business Solutions, Senior Manager of Business Development and Technical Sales at telecom service aggregator GetConnected, and Vice President of Strategic Partner Management at electronic payment processor IP Commerce. Prior to his tenure as OnePager's COO, Safford was the company's Vice President of Marketing and Alliances. Safford holds a BA in Psychology and management from Rice University.

2 thoughts on “Color-Coding by a Custom Status field in Microsoft Project

  1. I have set up links to my tasks, however they are not showing up. I am following all steps by going to task link and checkin the show task link.

Leave a Reply

Your email address will not be published. Required fields are marked *