Formula functions explained
You insert functions into formulae using the Function button on the Formula Properties dialog. A menu appears, from which you can choose the function that you want to insert into the formula.
Mathematical functions
You insert these functions into formulae to carry out mathematical functions on a field or value.
Round(value, decimal_places)
| Description | Rounds a value to a given number of decimal places. |
| Example |
Round((Cost / Income * 100), 2) & "%"
Shows costs as a percentage of income, rounded up or down (to the nearest value) to two decimal places. |
| Notes |
value is the value that requires rounding.
decimal_places is the required number of decimal places. This must be specified as a whole number; you can specify a negative whole number if you want to specify a digit to the left of the decimal separator. The result is flagged as a whole number if decimal_places is less than or equal to 0. |
Trunc(value, decimal_places)
| Description | Truncates a value to a given number of decimal places. |
| Example |
Trunc((Bar.Cost / Project.Cost * 100), 2) & "%"
Shows the costs of a bar as a percentage of project costs, truncated to two decimal places. |
| Notes | Similar to the Round function, except that this operation merely truncates the value rather than rounding up or down to the nearest value. This is equivalent to rounding down for positive numbers and rounding up for negative numbers. |
Mod(numerator, denominator)
| Description | Returns the floating point remainder of dividing the numerator by the denominator. |
| Notes | The result is never flagged as a whole number. |
Min(value, value)
| Description | Returns the minimum value from a comma-separated list of values. |
| Example 1 |
Min(2, 3, 1)
Returns a value of 1. |
| Example 2 |
Min(Cost(30), Cost(1080))
Examines the costs from the cost centre with an object ID of 30 and the costs from the cost centre with an object ID of 1080 and returns whichever cost is lowest. |
| Notes | This function can handle any number of parameters, which must all be of the same type and which must support the > and < operators. |
Max(value, value)
| Description | Returns the maximum value from a comma-separated list of values. |
| Example 1 |
Max(2, 3, 1)
Returns a value of 3. |
| Example 2 |
Max(Cost(30), Cost(1080))
Examines the costs from the cost centre with an object ID of 30 and the costs from the cost centre with an object ID of 1080 and returns whichever cost is highest. |
| Notes | This function can handle any number of parameters, which must all be of the same type and which must support the > and < operators. |
Text functions
Len(text)
You insert these functions into formulae to manipulate the text within values.
| Description | Returns the number of characters in a character string or in the contents of a field. |
| Example 1 |
Len("Powerproject")
Returns a value of 12. |
| Example 2 |
Len(Task.Name)
Returns a value of 10 for a task called "Groundwork". |
| Notes | If you use this function to calculate the length of a character string rather than the contents of a field, the character string must be enclosed within double quotation marks. |
Search(find_text, within_text)
| Description | Returns a number that indicates the start position of the find_text string within within_text. The search is not case-sensitive. |
| Example 1 |
Search("ground", Task.Name)
Returns a value of 1 for a task called "Groundwork", 6 for a task called "Underground" and 0 for a task called "Carpentry" (the zero result indicates that the find_text string cannot be found in the within_text). |
| Example 2 |
If(Search("issue", Notes) > 0, High, None)
A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue" (regardless of case), and no priority for all other tasks. |
| Notes | This function is useful for identifying tasks that have a particular text string contained within their name or notes. |
Find(find_text, within_text)
| Description | Returns a number that indicates the start position of the find_text string within within_text. The search is case-sensitive. |
| Example 1 |
Find("ground", Task.Name)
Returns a value of 1 for a task called "groundwork", 0 for a task called "Groundwork", 6 for a task called "Underground" and 0 for a task called "Carpentry" (the zero results indicate that the find_text string cannot be found in the within_text). |
| Example 2 |
If(Find("ISSUE", Notes) > 0, High, None)
A traffic light indicator that displays high priority for all tasks with notes that contain the word "ISSUE" in upper-case characters, and no priority for all other tasks. |
| Notes | This function is identical to the Search function, except for the fact that the search is case-sensitive. |
Type modification functions
You insert these functions into formulae to convert strings of one type into strings of another type.
Int(value)
| Description | Converts a numeric value or object reference to a whole number - ie a positive or negative number with no decimal places. |
| Example |
Int(123.2344)
Returns a value of 123. |
| Notes | You may want to use this function to convert a value into an integer before processing it further using another formula function. |
Text(value)
| Description | Converts a value to a character string. |
| Example |
If(Search("06/2012", Text(Task.Start)) > 0, High, None)
A traffic light indicator that displays high priority for all tasks with a start date that includes the string "06/2012" (ie those that start at some point in June 2012), and no priority for all other tasks. |
| Notes | You may want to use this function to convert a value into a character string in order to interrogate it using one of the Len, Search or Find functions, as in the example above. |
Value(text)
| Description | Converts the numeric elements of a character string to a value. |
| Example |
If(Allocation >= Value("10 People"), High, Low)
A traffic light indicator that displays high priority for all resource allocations with an allocation value greater than or equal to 10, and low priority for all other tasks. |
| Notes | You may want to use this function to convert the numeric elements of a character string into a value in order to compare it to another value, as in the example above. |
Hyperlink(text)
| Description | Converts a character string to a hyperlink, rendering it in blue with 'ALT-click to activate' behaviour. |
| Example 1 |
Hyperlink(Task.Notes)
Displays the contents of a task's Notes field and renders it in blue, with 'ALT-click to activate' behaviour. |
| Example 2 |
Hyperlink("http://www.astapowerproject.com")
Displays the above text string and renders it in blue, with 'ALT-click to activate' behaviour. |
| Notes | It is possible to insert URLs into user-defined fields and refer to them using spreadsheet fields. If a field containing a URL is used within a formula, the URL is recognised and rendered appropriately automatically. However, this identification and rendering is lost if an operation such as a concatenation (using the & operator) is performed on the field, resulting in a plain character string. This function is useful in such circumstances, as you can use it to identify that such a character string is a URL, causing it to be rendered appropriately once more. |
Conditional functions
You insert these functions into formulae to test for certain conditions.
If(expression 1, expression 2, expression 3)
| Description | Evaluates expression 1 and returns expression 2 if expression 1 is true, or expression 2 if expression 1 is false. |
| Example 1 |
If(Task.Name = "Roofing", High, None)
A traffic light indicator that displays high priority for all tasks called "Roofing", and no priority for all other tasks. |
| Example 2 |
If(Search("issue", Notes) > 0, High, None)
A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue", and no priority for all other tasks. |
Choose(option, match 1, expression 1, match 2, expression 2, ...)
| Description | Attempts to match option with one of the match parameters and returns the corresponding expression parameter. |
| Example |
Choose(Task.Name, "Groundwork", "Phase 1", "Brickwork", "Phase 2")
Displays "Phase 1" for tasks with a name of "Groundwork", "Phase 2" for tasks with a name of "Brickwork" and nothing for all other tasks. |
| Notes | You should enclose this function with a Try() function if there is a chance that the option parameter will not match any of the match parameters. |
Switch(test 1, expression 1, test 2, expression 2, ...)
| Description | Evaluates the test parameters in order and returns the expression parameter corresponding to the first test parameter that is true. |
| Example |
Switch(Cost < [~CB].Cost, "Under budget", Cost = [~CB].Cost, "On budget", Cost > [~CB].Cost, "Over budget")
Displays "Under budget" for tasks with costs less than those in the current baseline, "On budget" for tasks with costs equal to those in the current baseline and "Over budget" for tasks with costs greater than those in the current baseline. |
| Notes | You should enclose this function with a Try() function if there is a chance that none of the test parameters will be true. |
Error handling functions
There is a single error handling function - Try() - which you should use to surround formulae where there is a chance that the formula may return nothing. If a formula is not enclosed within the Try() function, it may display an error in such situations; if it is enclosed within the Try() function, you can specify what should be displayed if the formula returns nothing - or you can specify that nothing should be returned.
Try(expression, <alternative>)
| Description | Returns the result of the expression formula if that formula has no errors; if there is an error in the formula, the <alternative> parameter is returned if it has been specified, or nothing is returned if the <alternative> parameter has not been specified. |
| Example 1 |
Try(Choose(Task.Name, "Groundwork", "Phase 1", "Brickwork", "Phase 2"), "Other phase")
Displays "Phase 1" for tasks with a name of "Groundwork", "Phase 2" for tasks with a name of "Brickwork" and "Other phase" for all other tasks. |
| Example 2 |
Try(Choose(Task.Name, "Groundwork", "Phase 1", "Brickwork", "Phase 2"))
Displays "Phase 1" for tasks with a name of "Groundwork", "Phase 2" for tasks with a name of "Brickwork" and nothing for all other tasks (as no <alternative> parameter has been specified). |
| Notes | Errors can occur in formulae for a number of reasons. For example, you may be trying to reference an item of data that does not exist, or evaluating a parameter without taking into account the possibility that other values may be involved - as in the examples above. |
Currency conversion functions
There is a single currency conversion function - ConvCurrency() - which you can insert into formulae to convert a value into the same currency as another value.
ConvCurrency(value, currency, <baseline_reference>)
| Description | Converts the value parameter into the currency in which the currency parameter is specified. You can optionally specify a baseline in order to retrieve the conversion rate from a specific baseline. |
| Example 1 |
ConvCurrency(Cost, Income)
Converts an item's costs into the currency unit in which the item's income is specified and displays the converted costs in this currency. |
| Example 2 |
ConvCurrency(Cost, Income, CB)
Converts an item's costs into the currency unit in which the item's income is specified using the exchange rate that is defined against the currency unit in the current baseline, and displays the converted costs in this currency. |
| Notes | This function may be useful if, for example, the costs for a task are calculated in pounds but the income is calculated in dollars, and you want to display them both in the same currency. |
Date and duration functions
You insert these functions into formulae to manipulate dates and durations.
ConvDuration(duration, reference_duration, reference_date, <calendar>, <baseline_reference>)
| Description | Converts the duration parameter into the duration in which the reference_duration parameter is specified. You can optionally specify a calendar and a baseline in order to obtain the reference_duration and reference_date parameters from a specific calendar, optionally from a baseline. |
| Example |
ConvDuration(Summary.Duration, '1ed', Summary.Start, Summary.Calendar) - ConvDuration(Duration, '1ed', Summary.Start, Calendar)
Converts the duration of an item's parent summary task into elapsed days, according to the calendar that is applied to the item's summary task, converts the duration of the item itself into elapsed days, according to the calendar that is applied to the item itself, then subtracts the item's duration from the summary task's duration. Both conversions are evaluated with reference to the start date of the summary task. |
| Notes | This function may be useful if, for example, you use time unit overrides and you wish to convert a working duration to an elapsed duration with reference to a particular calendar before comparing it with a duration that may use another calendar, as in the example above. |
IsWorkingTime(duration)
| Description | Evaluates the duration of an object and indicates whether it has been entered in working or elapsed time. |
| Example |
IsWorkingTime(duration)
Displays a selected check box for objects with a duration that has been entered in working time, and a cleared check box for objects with a duration that has been entered in elapsed time (or in a mixture of working and elapsed time). |
IsElapsedTime(duration)
| Description | Evaluates the duration of an object and indicates whether it has been entered in working or elapsed time. |
| Example |
IsElapsedTime(duration)
Displays a selected check box for objects with a duration that has been entered in elapsed time, and a cleared check box for objects with a duration that has been entered in working time (or in a mixture of working and elapsed time). |
DateDiff(date 1, date 2, <calendar>, <baseline_reference>)
| Description | Returns the duration, calculated in working time according to the default calendar, between the two specified dates. You can optionally specify a different calendar and a baseline in order to calculate the difference between the two dates using a calendar other than the default, optionally from a baseline. |
| Example 1 |
DateDiff(Today, Task.Start)
Returns the duration between today's date and the start date of tasks, calculated in working time according to the default calendar. |
| Example 2 |
DateDiff(Task.Finish, YearEnd, 2557)
Returns the duration between the finish date of tasks and the year end, calculated in working time according to the calendar with an object ID of 2557. |
| Example 3 |
DateDiff(Task.Finish, YearEnd, Task.Calendar)
Returns the duration between the finish date of tasks and the year end, calculated in working time according to the calendar assigned to each task. |
| Example 4 |
DateDiff(ProjectStart, Task.Start, 2557, CB)
Returns the duration between the start date of the project and the start date of tasks, calculated in working time according to the calendar with an object ID of 2557, taken from the current baseline. |
| Notes | The result is negative if the date 1 parameter is later than the date 2 parameter. |
ElapsedDateDiff(date 1, date 2)
| Description | Returns the duration, calculated in elapsed time, between the two specified dates. |
| Example |
ElapsedDateDiff(Today, Task.Start)
Returns the duration between today's date and the start date of tasks, calculated in elapsed time. |
| Notes | The result is negative if the date 1 parameter is later than the date 2 parameter. |
AddDuration(date, duration, <calendar>, <baseline_reference>)
| Description | Adds the duration parameter to the date parameter and returns the resulting date. If the duration parameter is specified in working time, it is calculated according to the default calendar. You can optionally specify a different calendar and a baseline in order to calculate the specified duration using a calendar other than the default, optionally from a baseline. |
| Example 1 |
AddDuration(Task.Start, '1w')
Returns the date that results from adding 1 working week according to the default calendar to the start date of tasks. |
| Example 2 |
AddDuration(Task.Finish, '-3d')
Returns the date that results from subtracting 3 working days according to the default calendar from the finish date of tasks. |
| Example 3 |
AddDuration(Task.Start, '1ew')
Returns the date that results from adding 1 elapsed week to the start date of tasks. |
| Example 4 |
AddDuration(Task.Start, '1w', 2557)
Returns the date that results from adding 1 working week according to the calendar with an object ID of 2557 to the start date of tasks. |
| Example 5 |
AddDuration(Task.Start, '1w', Task.Calendar)
Returns the date that results from adding 1 working week according to the calendar assigned to each task to the start date of tasks. |
| Example 6 |
AddDuration(Task.Start, '1w', 2557, PB)
Returns the date that results from adding 1 working week according to the calendar with an object ID of 2557, taken from the project baseline, to the start date of tasks. |
| Example 7 |
AddDuration(Task.Start, '1w', 2557, 2
Returns the date that results from adding 1 working week according to the calendar with an object ID of 2557, taken from baseline number 2, to the start date of tasks. |
SetTime(date, <time>)
| Description | Returns the date specified in the date parameter, appended by the time specified in the <time> parameter. If the <time> parameter is omitted, the current time is appended to the date - and is updated whenever the view is refreshed. |
| Example 1 |
SetTime(Today)
Returns today's date, appended by the current time. |
| Example 2 |
SetTime(Task.Start, '09:45')
Returns the start date of tasks, appended by the time of 09:45. |
PeriodDate(progress_period)
| Description | Returns the report date of the progress period with the object ID specified in the progress_period parameter. |
| Example |
PeriodDate(4533)
Returns the report date of the progress period with an object ID of 4533. |
Context functions
You insert these functions into formulae to ascertain the type of object that is represented by a spreadsheet row.
ContextType()
| Description | Returns a value to reflect the type of object that each spreadsheet row represents: 0 for allocation rows (AllocationContext), 1 for task rows (TaskContext), 2 for bar rows (BarContext) and 3 for sort band rows (SortBandContext). You can use this function's enumerated values (given in parentheses above) to create formulae that display different things depending on the current context type, as illustrated in the second example below. |
| Example 1 |
ContextType()
Returns a numerical value to reflect the type of object that each spreadsheet row represents. |
| Example 2 |
If (ContextType() = AllocationContext, "Effort = " & Effort, "Duration = " & Duration)
Displays the effort figure for spreadsheet rows that represent allocations and the duration figure for spreadsheet rows that represent tasks, bars and sort bands. |
ObjectContext()
| Description | Returns the name of the object that each spreadsheet row represents. |
| Example |
"This is " & ObjectContext ()
Displays the text "This is ", followed by the name of the object that each spreadsheet row represents. |
BaselineContext()
| Description | Returns the baseline identifier for the current context, ie a positive number representing the baseline's ID or -1 (LP) to represent the main project. You can use this function to create formulae that display different things depending on particular baselines, or for baseline spreadsheet rows in general, as illustrated in the example below. |
| Example |
If(BaselineContext() = -1, Switch(Cost > '£10000', High, Cost > '£1000', Medium, Cost > '£100', Low, TRUE, None), None)
A traffic light indicator that displays high, medium or low priority for tasks in the live data dependent on their cost, and no priority for tasks in a baseline. |
Project and user-defined data functions
You insert the following functions into formulae to reference data from the project fields that are available on the Fields tab of the Properties dialog (available by clicking Advanced Properties on the Properties tab of the Backstage view) and to reference data from user-defined fields and tables.
ProjectBool(index)
| Description | Returns the value from the Boolean column on the Fields tab of the Properties dialog, in the row defined by the index parameter. |
| Example |
ProjectBool(3)
Displays a check box identical that in the Boolean column on the third row of the Fields tab of the Properties dialog. |
ProjectInteger(index)
| Description | Returns the value from the Integer column on the Fields tab of the Properties dialog, in the row defined by the index parameter. |
| Example |
ProjectInteger(6)
Returns the integer that is stored in the Integer column on the sixth row of the Fields tab of the Properties dialog. |
ProjectFloat(index)
| Description | Returns the value from the Float column on the Fields tab of the Properties dialog, in the row defined by the index parameter. |
| Example |
ProjectFloat(2)
Returns the number that is stored in the Float column on the second row of the Fields tab of the Properties dialog. |
ProjectDate(index)
| Description | Returns the value from the Date/Time column on the Fields tab of the Properties dialog, in the row defined by the index parameter. |
| Example |
ProjectDate(1)
Returns the date that is stored in the Date/Time column on the first row of the Fields tab of the Properties dialog. |
ProjectDuration(index)
| Description | Returns the value from the Duration column on the Fields tab of the Properties dialog, in the row defined by the index parameter. |
| Example |
ProjectDuration(8)
Returns the duration that is stored in the Duration column on the eighth row of the Fields tab of the Properties dialog. |
ProjectString(index)
| Description | Returns the value from the String column on the Fields tab of the Properties dialog, in the row defined by the index parameter. |
| Example |
ProjectString(5)
Returns the character string that is stored in the String column on the fifth row of the Fields tab of the Properties dialog. |
UDF(name)
| Description | Returns the contents of the user-defined field specified in the name parameter. |
| Example 1 |
UDF("My_field")
Returns the contents of the user-defined field called "My_field". |
| Example 2 |
If(Search("issue", UDF("My_field")) > 0, High, None)
A traffic light indicator that displays high priority for all tasks with a string-type user-defined field called "My_field" containing the word "issue", and no priority for all other tasks. |
| Notes | Note that the name of the user-defined field must be enclosed within double-quotation marks. |
UDT(name, column, <row_index>)
| Description | Returns the contents of the column field within the name user-defined table. If the <row index> parameter is omitted, the resulting character string contains all of the entries in the column in a comma-separated list; if the <row index> parameter is specified, the resulting character string contains the entry from the row of that number only. |
| Example 1 |
UDT("My_table", "My_column")
Returns the complete contents of the "My_column" field in the user-defined field called "My_table", with the entries presented in a comma-separated list. |
| Example 2 |
UDT("My_table", "My_column", 2)
Returns the contents of the second row of the "My_column" field in the user-defined field called "My_table". |
| Notes | Note that the names of the user-defined table and the column within it must be enclosed within double-quotation marks. |
UDE(name, index)
| Description | Within the user-defined enumerator specified in the name parameter, returns the name of the entry at the position specified by the index parameter. |
| Example |
UDE("My_enumerator", 3)
Returns the name of the third entry of the user-defined enumerator called "My_enumerator". |
| Notes | Note that the name of the user-defined enumerator must be enclosed within double-quotation marks. |
UDEIndex(name, text)
| Description | Within the user-defined enumerator specified in the name parameter, returns the number of the entry whose name has been entered in the text parameter. |
| Example |
UDEIndex("My_enumerator", "Type")
Returns the number of the "Type"-named entry of the user-defined enumerator called "My_enumerator". |
| Notes | Note that the name of the user-defined enumerator and the name of the entry must be enclosed within double-quotation marks. |