Calculated data expressions

You can use data expressions to define calculated custom data fields in Adobe Workfront. They connect existing Workfront fields in statements that generate a new field.

You can use calculated data expressions in:

  • A custom form

    For more information about creating calculated custom data fields on custom forms in Workfront, see Add calculated data to a custom form.

  • A calculated custom column in a report or list, when you use text mode

    For more information about using text mode in reports and views, see Text Mode overview.

Syntax of calculated custom fields vs. calculated custom columns

Although the functions that you use are the same, the syntax for building an expression in a calculated custom field can be different than it is for building a calculated custom column.

For example:

  • In a custom field, on a custom form for tasks, you would use the following to generate the name of the parent project of the task where the custom form is attached:

    {project}.{name}
    
  • In a custom column in a report, you would use the following to add a Project Name custom column on a task report:

    valuefield=project:name
    

    Or

    valueexpression={project}.{name}
    
    TIP

    The same syntax applies to all text-mode reporting elements where calculated expressions are used: views, filters, groupings, prompts.

The differences between the two syntaxes are:

Calculated custom field Calculated custom reporting element
Enclose field names in curly brackets. Do not enclose field names in brackets or parentheses when using them in a valuefield line.

Enclose field names in curly brackets when using them in a valueexpression line.

Separate the fields by periods.

Separate the fields by colons when using them in a valuefield line

Separate the fields by periods when using them in a valueexpression line.

For more information about the syntax you must use in a calculated custom column, see Text Mode overview.

Data expressions you can use

The lists below define the available expressions you can use when you are building one of the 3 different types of calculated custom fields in Workfront:

Date & time calculated custom fields

NOTE

If you create a date and time calculation that doesn’t include a time portion, or that uses the date wildcards $$TODAY or $$NOW, the system uses the date according to the Coordinated Universal Time (UTC) zone, not according your local timezone. This can cause an unexpected date result.

Expression Explanation and example
ADDDAYS

This expression adds the number of days to the date and is formatted as follows:

ADDDAYS(date, number)
ADDWEEKDAYS

This expression adds the number of weekdays to the date and is formatted as follows:

ADDWEEKDAYS(date, number)
ADDMONTHS

This expression adds the number of months to the date and is formatted as follows:

ADDMONTHS(date, number)
ADDYEARS

This expression adds the number of years to the date and is formatted as follows:

ADDYEARS(date, number)
CLEARTIME

This expression clears the time portion of a date and is formatted as follows. In this example, the date is the entry date for a work object.

CLEARTIME({entryDate})
DATE

This expression converts a string to a date and is formatted as follows:

DATE(string)
DATEDIFF

This expression returns the number of days between the two dates, taking into account the start and the end days of the period selected as well as the time stamps on those days. For example, if the start time of the start date is 3 PM, the start day will not be counted as a full day.

The expression is formatted as follows:

DATEDIFF(date1, date2)
DAYOFMONTH

This expression returns the day of month for the date as a number, between 1 and 31.

The expression is formatted as follows. In this example, the date is the entry date for a work object.

DAYOFMONTH({entryDate})
DAYOFWEEK

This expression returns the day of week for the date as a number, between 1 (Sunday) and 7 (Saturday).

The expression is formatted as follows. In this example, the date is the entry date for a work object.

DAYOFWEEK({entryDate})
DAYSINMONTH

This expression returns the total days in the month of the date as a number and is formatted as follows. In this example, the date is the entry date for a work object.

DAYSINMONTH({entryDate})
DAYSINSPLITWEEK

This expression returns the total weekdays between the date and the end of the week, or the end of the month, whichever comes first. In this example, the date is the entry date for a work object.

The expression is formatted as follows:

DAYSINSPLITWEEK({entryDate})
DAYSINYEAR

This expression returns the total days in the year of the date as a number and is formatted as follows. In this example, the date is the entry date for a work object.

DAYSINYEAR({entryDate})
DMAX

This expression returns the latest date in the list and is formatted as follows:

DMAX(date1, date2, ...)
DMIN

This expression returns the earliest date in the list and is formatted as follows:

DMIN(date1, date2, ...)
HOUR

This expression returns the hour of the date as a number between 0 and 23.

The expression is formatted as follows. In this example, the date is the entry date for a work object.

HOUR({entryDate})
MINUTE

This expression returns the minute of the date as a number between 0 and 60, formatted as follows. In this example, the date is the entry date for a work object.

MINUTE({entryDate})
MONTH

This expression returns the month of the date as a number between 1 and 12, formatted as follows. In this example, the date is the entry date for a work object.

MONTH({entryDate})
SECOND

This expression returns the second of the date as a number between 0 and 60, formatted as follows. In this example, the date is the entry date for a work object.

SECOND({entryDate})
WEEKDAYDIFF

This expression returns the number of weekdays between two dates, taking into account the start and the end days of the period selected as well as the time stamps on those days. For example, if the start time of the start date is 3 PM, the start day will not be counted as a full day.

The expression is formatted as follows:

WEEKDAYDIFF(date2, date1)
WORKMINUTESDIFF

This expression returns the number of scheduled minutes between the dates according to the default schedule.

The expression is formatted as follows:

WORKMINUTESDIFF(date1, date2)
YEAR

This expression returns the year of the date as a 4-digit number, formatted as follows. In this example, the date is the entry date for a work object.

YEAR({entryDate})

Mathematical calculated custom fields

Expression Explanation
ABS This expression returns the absolute value of the number and is formatted as follows. This example uses the number of objects below the object where the custom form is attached.
ABS({numberOfChildren})
AVERAGE This expression returns the average of numbers and is formatted as follows:
AVERAGE(number1, number2, ...)
CEIL This expression rounds a number up to the nearest integer and is formatted as follows. This example uses the number of objects below the object where the custom form is attached.
CEIL({numberOfChildren})
DIV This expression divides all the numbers in the order provided and is formatted as follows:
DIV(number1, number2, ...)
FLOOR This expression rounds a number down to the nearest integer and is formatted as follows. This example uses the number of objects below the object where the custom form is attached.
FLOOR({numberOfChildren})
LN This expression returns the natural logarithm value of the number and is formatted as follows:
LN({numberOfChildren})
LOG This expression returns the logarithm value of number2 to the base number1 and is formatted as follows:
LOG(number1, number2)
MAX This expression returns the largest item in the list and is formatted as follows:
MAX(item1, item2, ...)
MIN This expression returns the smallest item in the list and is formatted as follows:
MIN(item1, item2, ...)
NUMBER This expression converts a string to a number and is formatted as follows:
NUMBER(string)
POWER This expression returns a number raised to a power and is formatted as follows:
POWER(number, power)
PROD This expression multiplies all the numbers and is formatted as follows:
PROD(number1, number2, ....)
ROUND This expression rounds the number up to specified decimals of precision and is formatted as follows:

ROUND(number, precision)

SORTASCNUM

This expression orders the numbers in ascending order and is formatted as follows:

SORTASCNUM(number1,number2, ...)
SORTDESCNUM This expression orders the numbers in descending order and is formatted as follows:
SORTDESCNUM(number1, number2, ...)
SQRT

This expression returns a square root of a number and is formatted as follows. This example uses the number of objects below the object where the custom form is attached.

SQRT({numberOfChildren})
SUB This expression subtracts all numbers in the order provided and is formatted as follows:
SUB(number1, number2, ...)
SUM This expression adds all the numbers and is formatted as follows:
SUM(number1, number2, ...)

Text calculated custom fields

Expression Explanation
CASE

This expression is used with other expressions to choose a value from a list, based on a index number. An index number is a field or function that returns a numerical value (usually in a known range).

The expression is formatted as follows:

CASE(indexNumber, value1, value2, ...)

For example, the following expression returns the name of the day of the week, where 1=Sunday, 2=Monday, and so on, in a calculated column:

CASE(DAYOFWEEK({entryDate}),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

This expression works best with other expressions that return a number, such as DAYOFWEEK, DAYOFMONTH, and MONTH.

CONCAT

This expression concatenates the string and is formatted as follows:

CONCAT(string1,"separator", string2)

The following are examples of separators that you can include:

  • a space: " "
  • a dash: "-"
  • a slash: "/"
  • a comma: ","
  • a word: "or", "and"
CONTAINS This expression returns true if the findText string is found within the withinText string and is formatted as follows:
CONTAINS(findText, withinText)
ENCODEURL This expression escapes any special characters in the string so they an be included in a URL argument.

The expression is formatted as follows:

ENCODEURL(string)
IF

This expression evaluates a condition that you specify and returns the value of the trueExpression if it is true, or the value of the falseExpression if it is false.

The expression is formatted as follows:

IF(condition, trueExpression, falseExpression)

For example, you can compare two different date fields followed by a True/False result as a data string:

IF({projectedCompletionDate}>{plannedCompletionDate},"Off Track","On Track")

In everyday speech, this statement means: “IF the Projected Completion Date of my object is ‘Greater Than' the Planned Completion Date of my same object, then display the words ‘Off Track' in this field; otherwise, display the words ‘On Track.'”

If you do not want to label the true or false expressions, you must insert a blank label in your statement, such as:

IF({projectedCompletionDate}>{plannedCompletionDate},"","On Track")

Or

IF({projectedCompletionDate}>{plannedCompletionDate},"Off Track","")

For more information about building "IF" statements, see "IF" statements overview.

IFIN

This expression allows you to look for a specific value in a string of possible values. If the value you are looking for equals one of the provided values, then the expression returns the trueExpression; otherwise, it returns the falseExpression.

The expression is formatted as follows:

IFIN(value, value1, value2,..., trueExpression, falseExpression)

For example, you can find a specific Project Owner and mark those projects with a specified tag in a project view:
IFIN({owner}.{name},"Jennifer Campbell","Rick Kuvec","Marketing Team","Other Teams")

In everyday speech, this statement means: "If the Project Owner is Jennifer Campbell or Rick Kuvec, mark this project with 'Marketing Team'; otherwise, mark it with 'Other Teams'."

If you do not want to label the true or false expressions, you must insert a blank label in your statement, such as:

IFIN({owner}.{name},"Jennifer Campbell","Rick Kuvec","","Other Teams")

Or

IFIN({owner}.{name},"Jennifer Campbell","Rick Kuvec","Marketing Team",""

IN

This expression returns true if the value equals one of the provided values; otherwise, the expression returns false.

The expression is formatted as follows:

IN(value, value1[, value2...])
ISBLANK

This expression returns true if the value is null or empty; otherwise, the expression returns false.

The expression is formatted as follows:

ISBLANK(value)
LEFT

This expression returns a specified number of characters from the left side of a string and is formatted as follows:

LEFT(string, length)
LEN

This expression returns the length of a string and is formatted as follows:

LEN(string)
LOWER This expression returns the string in lower case and is formatted as follows:
LOWER(string)
REPLACE

This expression replaces all occurences of string2 with string3 in string1.

The expression is formatted as follows:

REPLACE(string1, string2, string3)
RIGHT

This expression returns a specified number of characters from the right side of a string and is formatted as follows:

RIGHT(string, length)
SEARCH

This expression returns the index of the first occurrence of findText in the string withinText, starting at the given start position, or -1 if the text is not found.

The expression is formatted as follows:

SEARCH(findText, withinText, start)
STRING

This expression converts a number to a string and is formatted as follows:

STRING(number)
SORTASCSTRING

This expression sorts a list of strings in ascending order and is formatted as follows:

SORTASCSTRING(string1, string2, ...)
SORTDESCSTRING

This expression sorts a list of strings in descending order and is formatted as follows:

SORTDESCSTRING(string1, string2, ...)
SUBSTR

This expression return characters of a string based upon the start and end index specified and is formatted as follows:

SUBSTR({string}, number of start position, length of string)
TRIM

This expression removes whitespace from the beginning and end of a string and is formatted as follows:

TRIM(string)
UPPER

This expression returns a string in upper case and is formatted as follows:

UPPER(string)

On this page