Working with Custom Field Formulas

The Fourth in a Series of Short Notes about Using Project VBA

Technically the formulas in customized fields are not VBA, but they are quite similar. With Project 2000 Microsoft added the capability to have a field display the results of a user-defined calculation. Until then, fields would only contain the value that the user put there. Needless to say, having the ability to have formulas was a big step forward. In fact, in some cases formulas are more useful than VBA macros are. The main reason is that they calculate automatically whereas a VBA macro needs to be executed either by calling the macro or tying it to some event (and events in Project are not what I'd consider robust). Because of this the field can display real-time information about a task.

Formulas in fields can with a little work control the formatting of your schedule as well. With a formula setting the value of a flag field, and a bar style which applies to tasks with that flag field set to "Yes" you can change what the gantt chart displays. There are also indicators which can be shown in the specific cells if the formula returns a particular value.

Of course there are some limitations to these formulas. They can only work with information from the particular task they are associated with and a handful of Project-level fields (Things like Project Start). In cases where you need information from other tasks a formula is not going to be sufficient. I've put together some guidelines about choosing one over the other. You can find them here.

Working with Formulas is pretty simple, but they are hidden rather deep in the interface. To get to them, right-click on a column header, choose customize fields, then choose the field you want the formula to apply to and click on the "formula" button. This brings up a dialog box where you can enter and edit the formula. Note that the = sign which is required for formulas in Excel is NOT REQUIRED and if you enter one you will get an error. After you have written the formula choose OK and you get back to the customize fields screen. At this point you have one more decision to make, you can decide whether the Summary tasks use the formula or not. The default is to not use the formula so be careful here if you want them to use the same calculation.

The variety of formulas is huge but here are some common situations people encounter in using formulas:

My formula refers to Baseline or Deadline fields.
When there is "NA" in the baseline or deadline it gives an error:

This problem is caused by the fact that the project gives a numerical value of 4294967296 (2 to the 32nd power - 1) if the field is "NA" (blank). Why it does this rather than giving a value of 0 I do not know, however once you know that it uses this number you can write a formula which accounts for it.

The solution is to use an iif statement. The syntax for an iif statement is as follows:

iif(expression, value if true, value if false)

So if you want to know if the difference between the baseline finish and the finish of a task you would use a formula like this (in a text field):

Iif([Baseline Finish] > 50000, "There is no baseline for this task", [Baseline Finish]-[Finish])

Another alternative is to use ProjDateValue to evaluate the data stored in the baseline. Since an empty baseline shows "NA" for dates such as Baseline Finish, you can test for it directly.

iif([Baseline Finish]=projdatevalue("NA"), "Has Baseline", "No, Baseline")


I am getting unexpected values when using work or duration in my formula.

The problem is usually caused by failing to convert the units correctly.
When you use duration or work in a formula Project uses the value of either in MINUTES. This can be confusing if you are subtracting a duration of 1 day from a duration of 2 days. You would expect that 2 - 1 = 1, but in Project it equals 480 minutes.

Now you may wonder why 480 minutes? There are 24 hours in a day x 60 minutes, however by default a Project day is 8 hours or 480 minutes. One easy way to handle this is to simply divide by 480 as in this example.

([Baseline Duration]-[Duration])/480 & " days"

You will then get the difference in days (note that using the & will concatenate the text within quotations to the result of the first part of the equation).
However there are times that a different project calendar is used and in that case a day may be defined as 24 hours or 7 hours. Because of this it is safer practice to use the constant [Minutes Per Day] or [Minutes Per Week] in the formula.

([Baseline Duration]-[Duration])/[Minutes Per Day] & " days"

I want to subtract one date from another in Project.

There are a number of ways to do date subtraction. The first is to simply subtract one from the other like this:

[Finish]-[Start]

On a one day task which starts and ends the same day this will return a value of .38 which is somewhat useful, but as in the section above it takes some conversion to make
sense of it. .38 days = 8 hours.
This approach also has some problems if you are subtracting across a non-working time such as a weekend or holiday. Or if the task ends on the next day. Then the value will be quite unexpected.

So there is another method that Project provides to do date math. It is to use the ProjDateDiff function. The syntax is as follows:

ProjDateDiff( date1, date2, calendar )

Using this will give you the difference between two dates according to a specific Project calendar. If you leave the calendar blank then it uses the Standard calendar. Otherwise you can specify the calendar (put the name of the calendar in quotations).
Here is an example of a calculation which finds the difference between the start and the finish of a task:

ProjDateDiff([Start],[Finish])

Note that the field order is different than the original equation. For a positive result you put the soonest date as the first parameter and the latest date as the second.