Formula field reference

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields.

The format is similar to the formula on a spreadsheet, so we advise you to search online for instructions and to use the same formula.

Overview

In a formula, you can reference fields (columns) by name. To return the value of an existing field you can simply input the name of that field:

Budget

So for example, if you wanted a formula that calculated a Used Budget based on your Budget and Remaining Budget fields, that would look something like:

Budget - Remaining Budget

Formulas may include parentheses () to change the order of operations:

(Budget - Remaing Budget) / Budget

Expressions

An expression is created when you combine values, fields, or formulas together and that combination evaluates to a single value. You can create simple expressions, like in the below example (the formula below would return a single value—so it's an expression):

Budget - Remaining Budget

You can also chain expressions together, this is especially helpful when using logical operators. For example, the technical pattern for the IF() formula is IF(expression, value1, value2). Taking our above example a step further, let's say we want to know when the Budget consumed of our item is over 100,000€:

IF(Budget - Remaining Budget > 100000; "This is over 100k€"; "This is less than 100k€")

Text operators and functions

To see the below text functions in action, and test them out yourself.

Text operators

Operator	Description	Examples
&	Concatenate text values into a single text value. Equivalent to CONCAT().	Project Title & “ Test” ⇒ Project Title Test

Functions

Operator	Description	Examples
ABS	Return absolute value	ABS(value)
AND	Return true if all arguments are true	AND(logical1, [logical2, ...])
ARRAYCONTAINS	Returns true if the array contains all values, otherwise returns false.	ARRAYCONTAINS(array; [...value]) ARRAYCONTAINS(Program; "Incubator") → true
ARRAYELEMENT	Return the element at the index of the array.	ARRAYELEMENT(array; index) ARRAYELEMENT(Program; 1) → Incubator
AVERAGE	Returns the (arithmetic) average of a set containing any value.	AVERAGE(number1, [number2, ...])
CEILING	Returns the nearest integer multiple of significance (1 if not provided) that is greater than or equal to value	CEILING(value)
CONCAT	Concatenate all arguments	CONCAT(value; [value2...])
CONTAINS	Returns true if argument 1 contains argument 2	CONTAINS(value; value2)
COUNT	Returns the number of values in the array.	COUNT(value1; [value2, ....]) COUNT(Program) → 5
DATE	Returns a date	DATE(year; month; day) DATE(2023; 05; 15) → 05/15/2023
DATEDIF	Calculates the number of days, months, or years between two dates.	DATEDIF(start_date; end_date, unit) DATEDIF(Creation date; Last project update; "D") → 10
EDATE	Returns a date before or after another date a specified number of months	EDATE(start_date; months)
EOMONTH	Returns a date representing the last day of a month which falls a specified number of months before or after another date.	EOMONTH(start_date; months)
EVEN	Rounds a number up to the nearest even integer.	EVEN(value)
FLOOR	Rounds a number down to the nearest integer.	FLOOR(value)
IF	Returns value1 if the logical argument is true, otherwise it returns value2	IF(Sales > 50;"Win"; "Lose") IF(logical_expression; value_if_true; value_if_false)
LEN	Returns the length of a string.	LEN(text)
LOWER	Converts a specified string to lowercase.	LOWER(text)
MAX	Return max in number list.	MAX(value1; [value2, ...])
MEDIAN	Return median in number list.	MEDIAN(value1, [value2, ...])
MIN	Return min in number list.	MIN(value1, [value2, ...])
MOD	Returns the result of the modulo operator, the remainder after a division operation.	MOD(dividend, divisor)
MONTH	Returns the month of the year a specific date falls in, in numeric format.	MONTH(date)
NETWORKDAYS	Returns the number of net working days between two provided days.	NETWORKDAYS(start_date; end_date) NETWORKDAYS(Creation date; Last project update) → 32
NOT	Return inverse of result	NOT(4 <> 4) → true NOT(logical_expression)
ODD	Rounds a number up to the nearest odd integer.	ODD(value)
OR	Return true if at least one argument is true.	OR(true; false) → true OR(logical_expression1; [logical_expression2, ...])
POWER	Returns a number raised to a power.	POWER(base, exponent)
REPLACE	Replaces part of a text string with a different text string.	REPLACE(text; stringToReplace; newString)
ROUND	Round a given number.	ROUND(value)
SQRT	Returns the positive square root of a positive number.	SQRT(value)
TRIM	Removes leading, trailing, and repeated spaces in text.	TRIM(text)
TRUNC	Truncates a number to a certain number of significant digits by omitting less significant digits.	TRUNC(value; [places])
UPPER	Converts a specified string to uppercase.	UPPER(text)
WEEKDAY	Returns a number representing the day of the week of the date provided.	WEEKDAY(date)
WEEKNUM	Returns a number representing the week of the year where the provided date falls.	WEEKNUM(date)
WORKDAY	Calculates the end date after a specified number of working days.	WORKDAY(Creation date; 2) → 18/06/2023 WORKDAY(start_date; num_days)
XOR	Return true if only one argument is true.	XOR(logical_expression1; [logical_expression2, ...])
YEAR	Returns the year specified by a given date.	YEAR(date)

Numeric operators

Operator	Description	Examples
+	Add together two numeric values	Budget + 200
-	Subtract two numeric values	Budget - 200
*	Multiply two numeric values	Budget * 2
/	Divide two numeric values	Budget / 2

Logical operators

Operator	Description	Examples
>	Greater than	3 > 2 => TRUE
<	Less than	2 < 3 => TRUE
>=	Greater than or equal to	3 >= 3 => TRUE
<=	Less than or equal to	2 <= 2 => TRUE
=	Equal to	2 = 2=> TRUE
<>	Is not equal to	3 <> 2 => TRUE