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:

You can use the expressions listed below to build calculated custom columns. However, you must use the correct syntax for a calculated custom column, as described in the section Syntax of calculated custom fields vs. calculated custom columns in this article.

Date and 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.

You can create a date or time calculated custom field using the following expressions:

ExpressionExplanation and example
ADDDAYS

Adds the number of days to the date. The number value can include partial days. For example, 1.5 adds one and a half days to the date.

The expression is formatted as follows:

ADDDAYS(date, number)

ADDWEEKDAYS

Adds the number of weekdays to the date. This expression only adds whole integer values to the date, rounding down.

The expression is formatted as follows:

ADDWEEKDAYS(date, number)

ADDMONTHS

Adds the number of months to the date and is formatted as follows:

ADDMONTHS(date, number)

ADDYEARS

Adds the number of years to the date and is formatted as follows:

ADDYEARS(date, number)

ADDHOURS

Adds the number of hours to the date and is formatted as follows:

ADDHOUR(date, number)

Note: This expression is not supported in Workfront Planning.

CLEARTIME

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

Converts a string to a date and is formatted as follows:

DATE(string)

DATEDIFF

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 is not counted as a full day.

The expression is formatted as follows:

DATEDIFF(date1, date2)

DAYOFMONTH

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

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

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

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

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

Returns the latest date in the list and is formatted as follows:

DMAX(date1, date2, ...)

DMIN

Returns the earliest date in the list and is formatted as follows:

DMIN(date1, date2, ...)

HOUR

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

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

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

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

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

Returns the number of scheduled minutes between the dates according to the default schedule.

The expression is formatted as follows:

WORKMINUTESDIFF(date1, date2)

YEAR

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

You can create a calculated custom field that that uses some of the following mathematical expressions:

ExpressionExplanation
ABS

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

Returns the average of numbers and is formatted as follows:

AVERAGE(number1, number2, ...)

CEIL

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

Divides all the numbers in the order provided and is formatted as follows:

DIV(number1, number2, ...)

FLOOR

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

Returns the natural logarithm value of the number and is formatted as follows:

LN({numberOfChildren})

LOG

Returns the logarithm value of number2 to the base number1 and is formatted as follows:

LOG(number1, number2)

MAX

Returns the largest item in the list and is formatted as follows:

MAX(item1, item2, ...)

MIN

Returns the smallest item in the list and is formatted as follows:

MIN(item1, item2, ...)

NUMBER

Converts a string to a number and is formatted as follows:

NUMBER(string)

POWER

Returns a number raised to a power and is formatted as follows:

POWER(number, power)

PROD

Multiplies all the numbers and is formatted as follows:

PROD(number1, number2, ....)

NOTE

When multiplying fields that contain hours, ensure that you understand whether the database saves the hours in selected fields in minutes, hours, or seconds. If the hours are saved in minutes or seconds but display in hours in the Workfront interface, you might need to account for the conversion from minutes or seconds to hours when writing an expression using this calculation.

ROUND

Rounds the number up to specified decimals of precision and is formatted as follows:

ROUND(number, precision)

SORTASCNUM

Orders the numbers in ascending order and is formatted as follows:

SORTASCNUM(number1,number2, ...)

SORTDESCNUM

Orders the numbers in descending order and is formatted as follows:

SORTDESCNUM(number1, number2, ...)

SQRT

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

Subtracts all numbers in the order provided and is formatted as follows:

SUB(number1, number2, ...)

SUM

Adds all the numbers and is formatted as follows:

SUM(number1, number2, ...)

Text calculated custom fields

You can create a calculated custom field that displays a text-formatted value using the following expressions:

ExpressionExplanation
ARRAY

Converts a string into an array. The delimiter can be any string.

The expression is formatted as follows:

ARRAY(string1, "delimiter")

ARRAYLENGTH

Returns the number of elements in the array and is formatted as follows:

ARRAYLENGTH(array)

ARRAYELEMENT

Returns the element at the specified number in the array. If the index is out of bounds, it returns empty.

The expression is formatted as follows:

ARRAYELEMENT(array, number)

SORTASCARRAY

Orders the array elements in ascending order and converts them to the type of the first element.

The expression is formatted as follows:

SORTASCARRAY(array)

For example, ["-12.6", -13.0] becomes ["-12.6", "-13"].

Note: This expression is not supported in Workfront Planning.

SORTDESCARRAY

Orders the array elements in descending order and converts them to the type of the first element.

The expression is formatted as follows:

SORTDESCARRAY(array)

For example, ["-12.6", -13.0] becomes ["-13", "-12.6"].

Note: This expression is not supported in Workfront Planning.

CASE

Is used with other expressions to choose a value from a list, based on an 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")

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

CONCAT

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

Returns true if the findText string is found within the withinText string and is formatted as follows:

CONTAINS(findText, withinText)

ENCODEURL

Escapes any special characters in the string so they can be included in a URL argument.

The expression is formatted as follows:

ENCODEURL(string)

FORMAT

Returns formatted text. Only the parameter options listed here are permitted with FORMAT.

The color options are $$POSITIVE, $$INFORMATIVE, $$NEGATIVE, $$NOTICE, and the other formatting options are $$BOLD, $$ITALIC, $$UNDERLINE. Only one color option is allowed, along with up to three other formatting options. If no color option is specified, the system's default color is applied.

The expression is formatted as follows:

FORMAT($$POSITIVE, $$BOLD, $$ITALIC)

Note: This expression is not supported in Workfront Planning.

IF

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

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

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

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

The expression is formatted as follows:

ISBLANK(value)

LEFT

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

LEFT(string, length)

LEN

Returns the length of a string and is formatted as follows:

LEN(string)

LOWER

Returns the string in lower case and is formatted as follows:

LOWER(string)

REPLACE

Replaces all occurences of string2 with string3 in string1.

The expression is formatted as follows:

REPLACE(string1, string2, string3)

RIGHT

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

RIGHT(string, length)

SEARCH

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

Converts a number to a string and is formatted as follows:

STRING(number)

SORTASCSTRING

Sorts a list of strings in ascending order and is formatted as follows:

SORTASCSTRING(string1, string2, ...)

SORTDESCSTRING

Sorts a list of strings in descending order and is formatted as follows:

SORTDESCSTRING(string1, string2, ...)

SUBSTR

Returns characters of a string based on the start and end index specified and is formatted as follows:

SUBSTR({string}, number of start position, number of end position)

SWITCH

Evaluates the expression against a list of values, and returns the result corresponding to the first matching value.

The epxression is formatted as follows:

SWITCH(expression, value1, result1, [value2, result2], ...)

This expression is not supported in Workfront Planning.

TRIM

Removes whitespace from the beginning and end of a string and is formatted as follows:

TRIM(string)

UPPER

Returns a string in upper case and is formatted as follows:

UPPER(string)

Previous pageCalculated custom data in reports
Next pageCalculated custom fields vs. calculated columns

Workfront


Learn: Automating Workflows with Workfront Fusion - Unique Use Cases in Action

Workfront

Tuesday, Mar 4, 6:00 PM UTC

Looking for creative ways to use Workfront Fusion to solve business challenges? Join Pan Shahbazian of Starbucks as she shares three unique use cases that can transform your workflows.

Register


Register to learn something new

WORKFRONT

Join Adobe product experts in live events where you will learn Adobe Workfront best practices, tips and tricks, and hear about the latest product features and updates.

Register