Jack Dahlgren's Microsoft Project Tools and Tips

Calculated Field FAQ's

Calculated fields are a feature in Project 2000 and greater which are very useful, however there are some conditions which cause confusion. This page addresses some of the common problems.

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.

Back to Main Page

Send me mail at:

JackD@mvps.org