Adding a Negative Total Slack Flag to MS Project

An improved version of this article is included in my free e-book, MS Project Hacks. Download the book and a sample MS Project file here. And scroll to the bottom to view a video lesson based on this article, created by the good folks at Webucator.

As you know, the critical path is the sequence of scheduled activities that determines the overall duration of the project. However, not all tasks are part of the critical path. The concept of slack time, also called float time, comes from the fact that some tasks can be performed sooner or later in time without impacting the finish date of the project. These tasks have an earliest possible start and a latest possible start, as well as an earliest and latest finish. The difference between the early and late finish dates is called the Total Slack. For tasks that are not part of the critical path, this is usually a positive value; for critical path tasks, it is should be zero. But that’s not always the case.

Setting Schedule Constraints

When planning or updating a project schedule, one of the most useful capabilities of Microsoft Project is the ability to set scheduling constraints. While most tasks will likely use Task Informationthe default, “As Soon As Possible,” there are times when a task has a constraint such as a required start or end date. When you set up a task with a fixed end date, you’ll go into the Advanced tab of the Task Information window, set the Constraint Type to “Finish No Later Than”, and enter the date.

Project will then apply that constraint, to determine whether you can actually meet that date with your current plan. If you’ve defined non-working time in your Standard calendar, specify it in the Calendar field. This will allow the scheduling algorithm to respect holidays. If the date math doesn’t work out, one or more tasks will have a negative Total Slack, which means that you won’t be able to finish by the required end date. In such a situation, it would help to have a flag that identifies tasks that have to be adjusted, in order to respect your constraints. Here’s how to add such a flag to your MS Project plan.

The Negative Total Slack Flag

Custom FieldsRight click on the column where you want to insert the status indicator. Click “Insert Column” and then select Flag 1. Right click on the newly inserted column and select “Custom Fields.” From here, you can rename the field (I chose “Sched” to keep it brief). Then under Custom Attributes, click on the radio button for Formula. You’ll get a warning message; that’s fine, because you really do want to calculate the values.

Then click on the Formula button, and paste in the following:

IIf([Total Slack]<0,True,False)

The Flag field contains a True or False value. Formula for SchedThis formula will calculate the field to be True when Total Slack is less than zero, so we can display our flag. Click OK, and then click on the Graphical Indicators button. This is where you choose the image to display.

Graphical IndicatorsYou really only need to define an image for the True value, and only for the Nonsummary rows. This will cause the flag to be visible only in the detail rows, where you can actually make schedule adjustments that will resolve the problems. Once you have chosen the image for your flag, click OK.

Your project plan should now look like this:

Project ScheduleTasks 1, 6, 7, 9, and 10 are flagged because they are on the critical path, and the total of their individual durations is greater than the number of work days between the project start date and the constrained end date. Note that the Total Slack calculation uses the Standard project calendar, so it only considers working days. If you have a holiday coming up, it takes that into account. Changing the duration of these critical path tasks, or changing the relationships between them to take one of them off the critical path, will change the total slack. When all of the flags are cleared, you have an achievable plan to get to your required end date.

Give it a try, and leave a comment if you find it useful, or find a bug, or think of an enhancement.

Crafting Your Own Formulas for Custom Fields in Microsoft Project

Programming NinjaAn improved version of this article is included in my free e-book, MS Project Hacks. Download the book and a sample MS Project file demonstrating the techniques shown here.

I’ve published several posts over the last couple of years on using custom fields in Microsoft Project. I’ve been gratified to see a number of folks adapt them to their specific needs, with modifications to the calculation formulas and other refinements. In fact, I got so much feedback on the original post on creating a calculated status field that I published a new post about three months ago, incorporating the most commonly requested changes. But, not all project managers are former programmers, and some folks have had trouble getting their refinements to work the way they want them to, so I figured I’d share a few tips on crafting your own formulas.

Parentheses and Brackets

Project uses parentheses in formulas to pass arguments to a function, and to group items in a calculation, much as you would in Algebra. You can “nest” parentheses, meaning you are allowed to do things like this:

IIf(Finish>Now(), IIf(Start>Now(),1,3),4)

In most programming languages (Lisp is the only exception I can think of), you must have an equal number of left and right parentheses; otherwise, the program interpreting the formula won’t understand what you’re trying to tell it. Project will tell you when you try to save a formula if it doesn’t understand, but its objections tend to be sketchy.

In some applications, single or double quotes support the use of field names that include a space character; Project uses square brackets. If you are using Project 2010 or later, you would write the example above like this:

IIf([Scheduled Finish]>Now(), IIf([Scheduled Start]>Now(),1,3),4)

To help me get things matched up, I use a programmer’s editor that highlights the parenthesis and its mate when I place my cursor on it. No highlight means there is no mate, so I know to look for a problem before I paste it into the calculation field in MS Project. Note that brackets are not nested; they should only appear at the beginning and end of a field name.

Passing Arguments to Functions

A function in a programming language is designed to receive zero or more specific arguments and return a value. As noted above, the arguments are listed within parentheses. In our example, the Now function doesn’t need arguments, so the list is empty; it merely returns today’s date. The IIF function, however, has three arguments. The first argument should be an equation that will evaluate to True or False. If True, it will evaluate whatever is in the second argument and return the value; if False, it will evaluate and return whatever is in the third argument. If you wanted to explain our example to someone, you might say, “If the scheduled finish date is not after today, return a 4. Otherwise, check to see if the scheduled start date is after today; if it is, return a 1, and if not, return a 3.”

Arguments are documented for every available function in a programming language. Typically, the function assumes a default value for a non-specified argument, if it isn’t required. MS Project requires all three arguments for the IIf function. However, other functions have different requirements. The Switch and Choose functions, for example, can have as many arguments as needed. Microsoft’s documentation on the available functions for custom fields in Project is available here.

I hope this quick guide is useful to you. If you don’t have already use one, Wikipedia has a high-level comparison of commonly available programmer’s editors. If you have other questions or want to correct an error, please leave me a comment.