Using formulas in custom fields

  • Article

 

Using formulas within custom fields has been a part of Microsoft Project for a long time, but typically they are used by the more advanced project managers and by developers of Project applications and add-ons.

 

That doesn't mean that you shouldn't learn how to use them. After all, of all the ways you can use to customize and automate Project, formulas within custom fields remain one of the simpler ones.

 

The first question that arises to those new to Project programming is, Why use a formula within a custom field in the first place?

 

The primary reason is that too often Project's default fields aren't displaying information precisely as you want. The second reason is that using formulas within custom fields is a much quicker and easier way to customize and automate Project than using VBA, VSTO, PowerShell, or C#.

 

Some Examples

  • For reporting purposes, if you'd rather have a text field and not a number field to display on the Gantt chart how much work resources are assigned to, you could create a custom text field, and then attach this formula to it:

 

    [work]/60/8 & "hours"

 

  1. To add the above formula, click Customize on the Tools menu, and then click Fields.
  2. Select a text field to customize, such as "Text1", and click Rename to give it a meaningful name, like "Work Time".
  3. Once you've renamed the field, click Formula.
  4. Copy and paste the above formula into the Edit Formula box.
  5. You're not done yet. After the formula has been added, go back to the Gantt chart and add the "Work Time" field.

 

 

  • Here's another example, When added to a custom number field, the following formula returns a numerical value that indicates the number of days between the current date and the finish date of the tasks in your schedule:

 

   DateDiff("d",NOW(),[Finish])

 

This example uses another method to customize Project fields by using functions. (don't confuse formulas with functions). Learn more about Project funcitons here.

 

 

  • A more complicated example: When added to a custom text field, the following formula (with functions included) returns a value of "No baseline," "Overbudget by 20% or more," or "Under budget":

Switch(Len(CStr([Baseline Finish]))<3, "No baseline", ([Cost]+1)/
([Baseline Cost]+1)>1.2,"Overbudget by 20% or more", ([Cost]+1)/([Baseline Cost]+1)>1,
"Overbudget",True,"Under budget")

 

An Example using VBA

 

Here is VBA code that does the same as the first example above. You might do this if you have numerous reports that need columns set in a specific way. The code could be added as a toolbar button. Adding custom fields using formulas within VBA is a four-step process. Here is an example that adds a "Work Value" column to the Gantt chart, with work values that are calculated by a formula.

 

  1. First, set up the custom formula that the custom field should use. In this example, the constant, pjCustomTaskText1, is being used to specify that a Text1 field is being customized to contain a formula.

 

   CustomFieldSetFormula FieldID:=pjCustomTaskText1, _

    Formula:="[Work]& "" hours"""

 

  1. Now calculate the formula.

 

   CustomFieldProperties FieldID:=pjCustomTaskText1, _

    Attribute:=pjFieldAttributeFormula, _

    SummaryCalc:=pjCalcFormula

 

  1. Now add the field to a view. "NewFieldName" indicates that Text1, will be used, which is equivalent to the pjCustomTaskText1 used in the CustomFieldProperties method above.

 

TableEdit name:="Entry", TaskTable:=True, NewName:="", fieldName:="", _ 

NewFieldName:="Text1", Title:="Work Table", ColumnPosition:=1

 

   

  1. Now apply the table to a view. I know, it sounds odd to apply the table when all you want to do is add a field, but that's the way it is with VBA code.

 

   TableApply name:="Entry"

 

There you have it. Not so difficult. And you saved the weekend.