Introduction to Formulas

Aspose.Tasks for .NET API supports reading and writing formulas in Microsoft Project (MPP) files.
Formulas are used to calculate custom values for tasks, resources, and project fields based on existing project data.

The Formula property of the ExtendedAttributeDefinition class provides an interface for assigning and reading formulas.
If an invalid formula string is assigned, a FormatException is thrown.

This page provides an introduction to the grammar used in formulas, supported operators, functions, and examples.

Formula Grammar

Microsoft Project uses the following grammar for formulas, which Aspose.Tasks also follows:

1    Formula -> Formula Binary_Op Primary | Primary
2    Primary -> (Formula) | Unary_Op Primary | Function | ValidFieldName | ValidDoubleValue | "StringLiteral"
3    Function -> ValidFunctionName(Args)
4    Args -> Args Delimiter Formula | Formula | |
5    Delimiter -> , | ;   // Note: Delimiter is culture-specific in XML format ( , or ; ) and persistent in MPP ( , )
6    Binary_Op -> + | - | * | / | & | MOD | ^ | = | <> | < | > | AND | OR
7    Unary_Op -> NOT | + | -

Supported Elements

1. Fields

You can use any valid Microsoft Project field name (e.g., [Duration], [Actual Duration], [Standard Rate]).

2. Operators

Formulas support:

Arithmetic operators: +, -, *, /, MOD, ^
Comparison operators: =, <>, <, >
Logical operators: AND, OR, NOT
String concatenation: &

3. Functions

Most standard Microsoft Project functions are supported, including:

Date and Time functions (e.g., DateDiff, Now)
Mathematical functions (e.g., Round, Abs, IIf)
String functions (e.g., Len, Instr, Left, Right)

Examples

Example 1: Simple Arithmetic

1var attr = ExtendedAttributeDefinition.CreateTaskDefinition(
2    CustomFieldType.Number,
3    ExtendedAttributeTask.Number1,
4    "WorkPerDay");
5
6attr.Formula = "[Work] / [Duration]";

This formula calculates the average work per day for a task.

Example 2: Conditional Expression

1var attr = ExtendedAttributeDefinition.CreateTaskDefinition(
2    CustomFieldType.Text,
3    ExtendedAttributeTask.Text1,
4    "TaskStatus");
5
6attr.Formula = "IIf([% Complete] = 100, \"Done\", \"In Progress\")";

This formula sets the custom field value to “Done” if the task is complete, otherwise to “In Progress”.

Error Handling

If a formula is syntactically incorrect, a FormatException will be thrown when assigning it.
If a formula refers to a non-existent field or function, it may cause an error during calculation.
Culture-specific delimiters (; vs ,) should be considered when working with XML project files. In MPP files, the delimiter is always a comma ,.