User Tools

Site Tools


formulas

Calculated Field Formulas

The value for a calculated field is determined by the field's formula. These formulas can reference other field's values or literal values. More complex results are possible using built-in functions.

Literal Values

Example Literal Description
true, TRUE True logical value.
false, FALSE False logical value.
“Text value” Literal text values are enclosed in quotes. If a quote is needed inside the literal, put a back-slash before the quote.
42.5 Constant numerical values are included by typing the number itself.

Field References

To reference the value of a field, enclose the field's reference name in brackets. For example:

[fieldRef]

Operators

Formulas can include the basic mathematical operators.

Operator Description
+ Add two numerical values.
- Subtract one numerical value from another.
* Multiply two numerical values.
/ Divide one numerical value into another.

Numerical Functions

Function Description
PRODUCT(number1,…,numberN) Multiply all the given numbers together.
SUM(number1,…,numberN) Add all the given numbers together.
MAX(number1,…,numberN) Maximum value amongst the given numbers.
MIN(number1,…,numberN) Minimum value amongst the given numbers.
POWER(base,power) Raise a given base number to the given power.

Text Functions

Function Description
CONCATENATE(text1,…,textN) Concatenate (join) multiple text values into a single text value.

Time and Date Functions

Function Description
DAYSBETWEEN(startDate,endDate) Calculates the number of days between 2 dates.
DATEADD(startDate,days) Add the given number of days to startDate.
CREATED() Date and time when the item was first created.
NOW() The current date and time.

Logical (Boolean) Functions

Function Description
WHENTRUE(logicalValue) Going back in time, tests when the given value was most recently set to true.
NOT(logicalValue) Calculates the inverse of the given logical value. If the value is TRUE, the result is FALSE. If the value is FALSE, the result is TRUE.
ISTRUE(logicalValue) Test if the given value is TRUE. If it is either FALSE or undefined, the result is FALSE.
ALLTRUE(logicalValue1,…,logicalValueN) Test if all the given values are TRUE. If any of the values are FALSE or undefined, the result is FALSE.
ANYTRUE(logicalValue1,…,logicalValueN) Test if any one of the given values are TRUE. If none of the values are TRUE, the result is FALSE.
ISSET(formula) Test if the result from the given formula is a defined value. If the result is undefined, the result is FALSE.
IF(logicalValue,resultIfTrue,resultIfFalse) Test a given logical value, returning the first result if the value is TRUE or the second value if the value is FALSE.

Comparison Functions

Function Description
GREATERTHANEQUAL(number1,number2) Returns true if number1 >= number 2, false otherwise. If either of the given numbers are undefined, an undefined result will be returned.
GREATERTHAN(number1,number2) Returns true if number1 > number 2, false otherwise. If either of the given numbers are undefined, an undefined result will be returned.
LESSTHANEQUAL(number1,number2) Returns true if number1 ⇐ number 2, false otherwise. If either of the given numbers are undefined, an undefined result will be returned.
LESSTHAN(number1,number2) Returns true if number1 < number 2, false otherwise. If either of the given numbers are undefined, an undefined result will be returned.
EQUAL(number1,number2) Returns true if number1 = number 2, false otherwise. If either of the given numbers are undefined, an undefined result will be returned.
formulas.txt · Last modified: 2018/04/03 20:08 by sroehling