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()) = 0, "Soon",
	
	([Start] - Now()) >= 31 AND ([Start]-Now()) >=0, "Future",
	
	([Start] - [Finish] > 0 AND ProjDateDiff([Start], Now()) / ProjDateDiff([Start], [Finish])) * 100 > [% Complete], "Late",
	
	([Start] - [Finish] > 0 AND 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: