Support for Evaluation of Functions in Formulas

Aspose.Tasks for .NET API supports evaluating functions defined as formulas inside Extended Attributes of a project.
This enables developers to perform calculations similar to those available in Microsoft Project, making it possible to implement dynamic fields that depend on custom logic.

The API provides evaluation support for the following categories of functions:

• Math functions
• General functions
• Text functions
• Date/Time functions

This feature is particularly useful when you need to:

• Automate project calculations,
• Implement custom business rules,
• Ensure formula compatibility between MS Project and Aspose.Tasks.

Calculation of Math Expressions

The following mathematical functions are supported:

1. Abs( number )
2. Atn( number )
3. Cos( number )
4. Exp( number )
5. Fix( number )
6. Int( number )
7. Log( number )
8. Rnd( number )
9. Sgn( number )
10. Sin( number )
11. Sqr( number )
12. Tan( number )

static void EvaluateSine()
{
    Project project = CreateTestProjectWithCustomField();
    
    // Set formula Sin(pi/2)
    project.ExtendedAttributes[0].Formula = "Sin(3.1415926/2)";

    // Print Calculated value
    Task task = project.RootTask.Children.GetById(1);
    Console.WriteLine("Sin(pi/2): {0}", task.ExtendedAttributes[0].NumericValue);
}

static Project CreateTestProjectWithCustomField()
{
    Project project = new Project();
    ExtendedAttributeDefinition attr = ExtendedAttributeDefinition.CreateTaskDefinition(CustomFieldType.Number, ExtendedAttributeTask.Number1, "Sine");
    project.ExtendedAttributes.Add(attr);

    Task task = project.RootTask.Children.Add("Task");

    ExtendedAttribute a = attr.CreateExtendedAttribute();
    task.ExtendedAttributes.Add(a);
    return project;
}

Calculation of General Functions

The following functions provide logical and conditional evaluations:

1. Choose( index, choice-1, choice-2, ... , choice-n ) – selects a value from a list.
2. IIf( expr, truepart, falsepart ) – returns one of two values depending on a condition.
3. IsNumeric( expression ) – checks whether an expression is numeric.
4. IsNull( expression ) – checks whether an expression is null.
5. Switch( expr-1, value-1, expr-2, value-2, ... ) – evaluates expressions in order and returns the first matching value.

static void EvaluateChoose()
{    
    Project project = CreateTestProjectWithCustomField();
    
    // Set Formula
    project.ExtendedAttributes[0].Formula = "Choose(3, \"This is a\", \"right\", \"choice\")";
    
    // Print extended attribute value
    Task task = project.RootTask.Children.GetById(1);
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
}

static void EvaluateIsNumeric()
{
    string[] numericFormulas = { "IsNumeric('AAA')", "IsNumeric(1)", "IsNumeric(1<0)", "IsNumeric(\"1.1\")", "IsNumeric(Choose((2 + Sgn(2^-3)), 123, \"one two three\"))" };
    
    Project project = CreateTestProjectWithCustomField();
    
    foreach (string numericFormula in numericFormulas)    
    {
        // Set Formula
        project.ExtendedAttributes[0].Formula = numericFormula;

        // Print extended attribute value
        Task task = project.RootTask.Children.GetById(1);
        Console.WriteLine(task.ExtendedAttributes[0].TextValue);
    }
}

static void EvaluateSwitch()
{    
    Project project = CreateTestProjectWithCustomField();

    // Set Formula
    project.ExtendedAttributes[0].Formula = "Switch( 0 < 1, \"0 is lesser than 1\", 0 > 1, \"0 is greater than 1\")";

    // Print extended attribute value
    Task task = project.RootTask.Children.GetById(1);
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
}

Calculation of Text Functions

String manipulation functions allow formatting and comparison:

1. Asc( string ) – returns the ASCII code of the first character.
2. Chr( charcode ) – returns the character for an ASCII code.
3. Format( expression, format, firstdayofweek, firstweekofyear ) – formats numbers/dates.
4. Instr( start, string1, string2, compare ) – finds the position of one string within another.
5. LCase( string ) – converts to lowercase.
6. Left( string, length ) – extracts leftmost characters.
7. Len( string ) – returns string length.
8. LTrim( string ) – removes leading spaces.
9. Mid( string, start, length ) – extracts substring.
10. Right( string, length ) – extracts rightmost characters.
11. RTrim( string ) – removes trailing spaces.
12. Space( number ) – returns a string with spaces.
13. StrComp( string1, string2, compare ) – compares two strings.
14. StrConv( string, conversion, LCID ) – converts string according to locale.
15. String( number, character ) – returns a string repeated n times.
16. Trim( string ) – removes leading and trailing spaces.
17. UCase( string ) – converts to uppercase.

static void EvaluateStrConv()
{
    Project project = CreateTestProjectWithCustomField();
    Task task = project.RootTask.Children.GetById(1);

    // Set formulas and print extended attribute value
    project.ExtendedAttributes[0].Formula = "StrConv(\"sTring and sTRINg\",3)";
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
    project.ExtendedAttributes[0].Formula = "StrConv(\"sTring and sTRINg\",1)";
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
    project.ExtendedAttributes[0].Formula = "StrConv(\"sTring and sTRINg\",2)";
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
}

static void EvaluateStringFunction()
{
    Project project = CreateTestProjectWithCustomField();
    Task task = project.RootTask.Children.GetById(1);

    // Set formulas and print extended attribute value
    project.ExtendedAttributes[0].Formula = "String(5, 40)";
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
    project.ExtendedAttributes[0].Formula = "String(5, \"A\")";
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
    project.ExtendedAttributes[0].Formula = "String(-5, \"A\")";
    // #Error
    Console.WriteLine(task.ExtendedAttributes[0].TextValue);
}

Calculation of Date/Time Functions

Aspose.Tasks supports both standard VB-style date functions and MS Project–specific scheduling functions:

1. CDate( expression ) – converts expression to a date.
2. Date() – returns the current system date.
3. DateAdd( interval, number, date ) – adds interval to a date.
4. DateDiff( interval, date1, date2 ) – difference between two dates.
5. DatePart( interval, date ) – extracts part of a date (year, month, day, etc.).
6. DateSerial( year, month, day ) – builds a date from components.
7. DateValue( date ) – converts string to date.
8. Day( date ) – day of the month.
9. Hour( time ) – extracts hour.
10. IsDate( expression ) – checks if expression is a valid date.
11. Minute( time ) – extracts minutes.
12. Month( date ) – extracts month.
13. Now() – returns current date and time.
14. ProjDateAdd( date, duration, calendar ) – adds duration considering project calendar.
15. ProjDateConv( expression, dateformat ) – converts date to project format.
16. ProjDateDiff( date1, date2, calendar ) – difference considering project calendar.
17. ProjDateSub( date, duration, calendar ) – subtracts duration.
18. ProjDateValue( expression ) – evaluates a date expression.
19. ProjDurConv( expression, durationunits ) – converts durations between units.
20. ProjDurValue( duration ) – evaluates duration expression.
21. Second( time ) – extracts seconds.
22. Time() – returns current system time.
23. Timer() – number of seconds since midnight.
24. TimeSerial( hour, minute, second ) – builds a time value.
25. TimeValue( time ) – converts to a time.
26. Weekday( date, firstdayofweek ) – returns the day of week.
27. Year( date ) – extracts year.

Project project = CreateTestProject();
Task task = project.RootTask.Children.GetById(1);

ExtendedAttributeDefinition numberDefinition = ExtendedAttributeDefinition.CreateTaskDefinition(ExtendedAttributeTask.Number1, null);
project.ExtendedAttributes.Add(numberDefinition);

ExtendedAttribute numberAttribute = numberDefinition.CreateExtendedAttribute();
task.ExtendedAttributes.Add(numberAttribute);

// Set ProjDateDiff formula and print extended attribute value
numberDefinition.Formula = "ProjDateDiff(\"03/23/2015\",\"03/18/2015\")";
Console.WriteLine(numberAttribute.NumericValue);
numberDefinition.Formula = "ProjDateDiff(\"03/23/2015\",\"03/25/2015\")";
Console.WriteLine(numberAttribute.NumericValue);

ExtendedAttributeDefinition dateDefinition = ExtendedAttributeDefinition.CreateTaskDefinition(ExtendedAttributeTask.Date1, null);
project.ExtendedAttributes.Add(dateDefinition);
ExtendedAttribute dateAttribute = dateDefinition.CreateExtendedAttribute();
task.ExtendedAttributes.Add(dateAttribute);

ExtendedAttributeDefinition durationDefinition = ExtendedAttributeDefinition.CreateTaskDefinition(ExtendedAttributeTask.Duration4, "Custom duration field");
project.ExtendedAttributes.Add(durationDefinition);
ExtendedAttribute durationAttribute = durationDefinition.CreateExtendedAttribute();
task.ExtendedAttributes.Add(durationAttribute);

ExtendedAttributeDefinition textDefinition = ExtendedAttributeDefinition.CreateTaskDefinition(ExtendedAttributeTask.Text5, "Custom text field");
project.ExtendedAttributes.Add(textDefinition);
ExtendedAttribute textAttribute = textDefinition.CreateExtendedAttribute();
task.ExtendedAttributes.Add(textAttribute);

// Set ProjDateSub formula and print extended attribute value
dateDefinition.Formula = "ProjDateSub(\"3/19/2015\", \"1d\")";
Console.WriteLine(dateAttribute.DateValue);

// We can set ProjDurConv formula to duration-valued attribute as well as to text-valued attribute.

// Set ProjDurConv formula to duration-valued extended attribute and print its value.
durationDefinition.Formula = "ProjDurConv([Duration], pjHours)";
Console.WriteLine(durationAttribute.DurationValue);

// Set ProjDurConv formula to text-valued extended attribute and print its value.
textDefinition.Formula = "ProjDurConv([Duration], pjHours)";
Console.WriteLine(textAttribute.TextValue);

textDefinition.Formula = "ProjDurConv([Duration], pjWeeks)";
Console.WriteLine(textAttribute.TextValue);

// Set Second formula and print entended attribute value
numberDefinition.Formula = "Second(\"4/21/2015 2:53:41 AM\")";
Console.WriteLine(numberAttribute.NumericValue);

// Set Weekday formula and print entended attribute value
numberDefinition.Formula = "Weekday(\"24/3/2015\", 1)";
Console.WriteLine(numberAttribute.NumericValue);
numberDefinition.Formula = "Weekday(\"24/3/2015\", 2)";
Console.WriteLine(numberAttribute.NumericValue);
numberDefinition.Formula = "Weekday(\"24/3/2015\", 3)";
Console.WriteLine(numberAttribute.NumericValue);

Practical Example

Here is a real-world scenario of using formulas:

    var project = new Project();
    var task = project.RootTask.Children.Add("Task 1");

    // Create an extended attribute with a custom formula
    ExtendedAttributeDefinition attrDef = ExtendedAttributeDefinition.CreateTaskDefinition(
        ExtendedAttributeTask.Text1, "Custom Formula");
        attrDef.Formula = "IIf([Duration] > 480, \"Long Task\", \"Short Task\")";
        project.ExtendedAttributes.Add(attrDef);

    var attr = attrDef.CreateExtendedAttribute();
    task.ExtendedAttributes.Add(attr);

    // The formula is automatically evaluated by Aspose.Tasks
    Console.WriteLine(task.Get(Tsk.Name) + " -> " + task.ExtendedAttributes[0].TextValue);

Key Benefits of Formula Support

• Full compatibility with MS Project formulas.
• Ability to automate dynamic fields and conditional logic.
• Built-in support for project calendar-aware functions (ProjDateAdd, ProjDateDiff, etc.).
• Simplifies complex calculations without writing custom C# code.

See Also

• Working with Extended Attributes
• Working with Formulas
• Working with Project Calculations