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 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:
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.