Follow
Understanding Calculated Data Expressions

Data expressions are used when defining calculated custom data fields in Workfront. They connect existing Workfront fields in statements that generate a new field.

Understanding Calculated Data Expressions

You can use calculated data expressions in the following areas of Workfront:

  • In a custom form
    For more information about creating calculated custom data fields on custom forms in Workfront, see "Using Calculated Custom Data."
  • In 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 "Understanding Text Mode."

There are a number of expression that can be used in calculated custom fields. The following list defines the available data expressions in Workfront:

Expression Example  Explanation Type
ADDDAYS ADDDAYS(date, number) Add number of days to date Date & Time
ADDWEEKDAYS ADDWEEKDAYS(date, number) Add number of week days to a date

Date & Time

ADDMONTHS ADDMONTHS(date, number) Add number of months to date

Date & Time

ADDYEARS ADDYEARS(date, number) Add number of years to date Date & Time
CLEARTIME CLEARTIME(date) Clears the time portion of a date Date & Time
DATE DATE(string) Converts a string to a date
For example, when referencing a custom field that is formatted as Text, but it holds a date value, you can reference that value with this expression so you can use it in date functions.
Date & Time
DATEDIFF DATEDIFF(date1, date2) 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.  Date & Time
DAYOFMONTH DAYOFMONTH(date) Returns the day of month for the given date as a number, between 1 and 31.  Date & Time
DAYOFWEEK DAYOFWEEK(date) Returns the day of week for the given date as a number, between 1 (Sunday) and 7 (Saturday) Date & Time
DAYSINMONTH DAYSINMONTH(date) Returns the total days in the month of the given date as a number Date & Time
DAYSINSPLITWEEK DAYSINSPLITWEEK(date) Returns the total week days between the date and the end of the week, or the end of the month, which ever comes first  Date & Time
DAYSINYEAR DAYSINYEAR(date) Returns the total days in the year of the given date as a number Date & Time
DMAX DMAX(date1, date2, ...) Returns the latest date in the list Date & Time
DMIN DMIN(date1, date2, ...) Returns the earliest date in the list Date & Time
HOUR HOUR(date) Returns the hour of the given date as a number between 0 and 23 Date & Time
MINUTE MINUTE(date) Returns the minute of the given date as a number Date & Time
MONTH MONTH(date) Returns the month of the given date as a number Date & Time
SECOND SECOND(date) Returns the second of the given date as a number Date & Time
WEEKDAYDIFF WEEKDAYDIFF(date1, date2) Returns the number of week days 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.  Date & Time
WORKMINUTESDIFF  WORKMINUTESDIFF(date1, date2) Returns the number of scheduled minutes between the dates according to the default schedule  Date & Time
YEAR YEAR(date) Returns the year of the given date a number Date & Time
ABS ABS(number) Returns the absolute value of the number Mathematical
AVERAGE AVERAGE(number1, number2, ...) Returns the average of numbers  Mathematical
CEIL CEIL(number) Rounds a number up to the nearest integer Mathematical
DIV DIV(number1, number2, ...) Divides all the numbers in the order provided Mathematical
FLOOR FLOOR(number) Rounds a number down to the nearest integer Mathematical
LN LN(number) Returns natural logarithm value of the number Mathematical
LOG LOG(number1, number2) Returns logarithm value of number2 to the base number1 Mathematical
MAX MAX(item1, item2, ...) Returns the largest item in the list Mathematical
MIN MIN(item1, item2, ...) Returns the smallest item in the list Mathematical
NUMBER NUMBER(string) Converts a string to a number.
For example, when referencing a custom field that is formatted as Text, but it holds a numerical value, you can reference that value with this expression so you can use it in mathematical calculations.
Mathematical
POWER POWER(number, power) Returns a number raised to a power Mathematical
PROD PROD(number1, number2, ....) Multiples all the numbers Mathematical
ROUND ROUND(number, precision) Rounds the number up to specified decimals of precision Mathematical
SORTASCNUM SORTASCNUM(number1,number2,...) Orders the numbers in ascending order. Mathematical
SORTDESCNUM SORTDESCNUM(number1, number2,...) Orders the numbers in descending order. Mathematical
SQRT SQRT(number) Returns a square root of a number Mathematical
SUB SUB(number1, number2, ...) Subtracts all numbers in the order provided Mathematical
SUM SUM(number1, number2, ...) Adds all the numbers Mathematical
CONCAT CONCAT(string1,"separator", string2)

Concatenates the string. Examples of separators that you can include:

  • a space: " "
  • a dash: "-"
  • a slash: "/"
  • a comma: ","
  • a word: "or", "and"
Text
CONTAINS CONTAINS(findText, withinText) Returns true if the findText string is found within the withinText string Text
ENCODEURL ENCODEURL(string) Escapes any special characters in the string so they an be included in a URL argument Text
LEFT LEFT(string, length) Returns a specified number of characters from the left side of a string Text
LEN LEN(string) Returns the length of a string Text
LOWER LOWER(string) Returns the string in lower case Text
REPLACE REPLACE(string1, string2, string3) Replaces all occurences of string2 with string3 in string1 Text
RIGHT RIGHT(string, length) Returns a specified number of characters from the right side of a string Text
SEARCH SEARCH(findText, withinText, start) Returns the index of the first occurence of findText in the string withinText, starting at the given start position, or -1 if th text is not found Text
STRING STRING(number) Converts a number to a string Text
SORTASCSTRING SORTASCSTRING(string1, string2, ...) Sorts a list of strings in ascending order. Text
SORTDESCSTRING SORTDESCSTRING(string1, string2, ...)  Sorts a list of strings in descending order. Text
SUBSTR

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

Return characters of a string based upon the start and end index specified Text
TRIM TRIM(string) Removes whitespace from the beginning and end of a string Text
UPPER UPPER(string) Returns a string in upper case Text
CASE CASE(indexNumber, value1, value2, ...)

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).
For 
example, the following expression will return the name of the day of the week, where 1=Sunday, 2=Monday, etc 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. For example, DAYOFWEEK, DAYOFMONTH, MONTH. 

Text
IF IF(condition, trueExpression, falseExpression)

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. 

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. For example: 

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


Or

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

 

For more information about building "IF" statements, see ""IF" Statements Overview."

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

This expression allows you to look for a specific values in a string of possible values. If the value you are looking for equals one of the value1, value2, etc then the expression returns the trueExpression, otherwise returns 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. For example: 

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


Or

IFIN({owner}.{name},"Jennifer Campbell","Rick Kuvec","Marketing Team","")
Text
IN IN(value, value1, value2...) Returns true if value equals one of the provided value1, value2..., otherwise false Text
ISBLANK ISBLANK(value) Returns true if the value is null or empty, false otherwise Text

Understanding the Syntax of Calculated Data Expressions

Although the data expressions used are the same, the syntax for building a calculated custom field is different than that of building a calculated custom column.

For example:

  • To add a calculated data expression to a custom field on a task form for the Project Name, you use the following text:
    Project.Name
  • To add a calculated data expression to a custom column on a task report for the Project Name, you use the following text:
    valuefield=project:name
    Or
    valueexpression={Project}.{Name}

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

This article last updated on 2019-02-25 13:53:46 UTC