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

## Comments (8)

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

The formula I actually use is:

IIf([Actual Finish]>50000,"Incomplete",[Duration7]-[Actual Duration]), where [Duration7] is a user input duration

But the value still shows "#Error" for rows which [Actual Finish]= NA. I even tried to use projdatevalue("NA"), but no difference.

Posted by Morgan Chan | August 30, 2006 9:52 PM

Posted on August 30, 2006 21:52

Hi,

I'm trying to set the finsh date based on the input of another field. I created a cutom field using a drop-down menu. When the user selects a value from this menu, I want to set the finish date to a pre-determined date. Example: If text 1 =J350, then finish date = 12/28/06. Ideally, a "look-up table" (below) would be imported so I could use it on multiple schedules across a PMO.

J350 12/20/06

J355 1/6/07

J360 2/4/07

J370 3/6/07

Etc....

Thanks in advance.............devin

I don't have project here so I can't check definitively, but I think that you can do this with an outline code field without having to use a custom field formula. I think they have both a value and a title so you can set the value to one thing and the title to the other.Of course you could use a custom field formula, BUT if there are a lot of these you will rapidly run out of characters for your formula unless there is a simple mathematical formula which relates the two. For example if J350 = now and J351 = next week, then you could write a formula which takes the part to the right of the J, subtracts 350 from it. multiplies by 7 and adds that to whatever hardcoded base date you have for now. If there is not a mathematical relationship then you need a switch statement with each value called out.

Finally, if the relationship between the J number and the date is complicated you can use some VBA to put the value there.

You should not that the finish date of a task can not use a custom field formula. It is calculated by project. The only way to set it is through a VBA macro. I'd caution against setting a finish date because it will set a bunch of constraints in your project, but with the right code you could extend the duration of the activity until it hits the desired finish date.

-Jack

Posted by devin | December 16, 2006 1:31 PM

Posted on December 16, 2006 13:31

I require a kind field that allowable sumary by EDS and display the values in percent "0.00%". Are there others way to format the personal field?.

Best regards.

Posted by Iván Abril | June 27, 2007 2:32 PM

Posted on June 27, 2007 14:32

I just recently came across the date anomaly in MS Project, having never used the formulas before. Someone was nice enough to point me to this blog/page. 2^32-1 is an FFFFFFFE in hex, which is a -2 in two's complement notation for a 32 bit number.

(It's a negative one in 1's complement)

00000000 to 7FFFFFFF are positive numbers from 0 to 134217727

So if they use a 4byte to represent # of days from some arbitrary past, these numbers are big enough.

I would suspect that some programmer used the values of ( -1 ) to mean something at one time, ( -2 ) to mean not filled in, etc.

Just some thoughts by an old programmer.

Posted by Steve | June 28, 2007 9:47 AM

Posted on June 28, 2007 09:47

I need to display additional time zones as customised start and finish columnsin my msproject schedule to show both NZ and AUS time. But I can not work out HOW to create a customized field to display this information in a column. Can anyone give me the syntax for the formula - or an example?

Also... because life is never simple, halfway through my project we have daylight saving starting so my full requirement for the formula is:

if [start] is before 30/09/07 2:00 AM then [start1] = [start] -2hrs else if [start] is after 30/09/07 2:00 AM then [start1] = [start] -3hr.

(and yes we are working 24hrs and weekends).

I would be grateful for any help or pointers.

Posted by Aisling McCarthy | July 10, 2007 6:51 PM

Posted on July 10, 2007 18:51

I have tried using 4294967296 (2 to the 32nd power - 1) to test the [Actual Start] field for "NA" (blank) and it still returns an #ERROR. Any ideas or suggestions?

------------------------------------------------

Try using Projdatevalue("NA") as the criteria instead of the big number. It is less likely to break if project changes the way the data is stored. The fact that the number is 2^32 - 1 is just a curiousity. Don't depend on it. -JackPosted by Dan Hrnack | July 25, 2007 7:23 AM

Posted on July 25, 2007 07:23

I would like the Marked field to change between Yes and No depending on the value of another field (Text field). I have not figured out how to make this happen.

Posted by Paul Atencio | July 31, 2007 12:41 PM

Posted on July 31, 2007 12:41

I am trying to copy a finish date from a task to a custom field. For example

MyCustomDate = PA.FinishDate

Does anyone know how to go about doing this? I am trying the formula field for an Enterprise Custom Field.

Thanks!

Posted by Jim | August 21, 2008 9:30 AM

Posted on August 21, 2008 09:30