Formulas

A formula field contains a value that is calculated from a spreadsheet-like formula. They can perform operations on non-formula fields on the same record, allowing for mathematical, logical, text & date based processing. For example, a full name field could be added to the User collection that concatenates the first name and last name fields.
Creating a formula field
On the data tab of your portal navigate to the collection you would like to add the formula field to
Add a new field and choose Formula as the type.
Enter the formula. To reference another field hit the plus button to add the reference at the current location. 
When do they calculate?
A formula's value for a given record will calculate when any of the following occur:
When the formula field is created
When the formula is updated
When a new record is added
When a field referenced in a formula is updated
If a formula doesn't reference any other fields it will only be calculated for a given record when:
A formula is added initially
The formula is edited
The record is initially created
Output Types
When a formula is added or updated, we will check the output and assign a type based on the output patterns. The possible outputs are:
Boolean (formula must return true or false)
Date (formula must return value wrapped in TODATE operator)
Decimal
Integer
Text
Examples
Field references will be highlighted green in the below examples
Joining text fields
E.g. creating a full name from a first name & last name, ensuring to add a space in between if both fields have a value
first name &
IF(OR(ISBLANK(first name),ISBLANK(last name)),""," ")
& last name
Replacing part of a text field
E.g. replace "-old" with " (discontinued)" for a nicer record view
SUBSTITUTE(name, "-old", " (discontinued)")
Adding days to a date
E.g. creating a deadline a week after an important date
TODATE(important date + 7) 
Calculations
E.g. calculating the gross sales
(number sold - number refunded) * price
Supported Operations
Below is a reference of the officially supported operators in our functions.
Dates & Time
Operator Name
Description
Usage
+
Adds a number of days
date + 1
-
Subtracts a number of days
date - 2
TODATE
Converts a date to the correct output format for a Noloco date.
TODATE(date)
DATE
Converts a provided day, month and year to a serial date.
DATE(year, month, day)
DATEVALUE
Converts a date from text in one of the two formats:
"mm-dd-yyyy" or "yyyy-mm-dd" to a serial date.
DATEVALUE(dateText)
DAY
The day from a date
DAY(date)
DAYS
Number of days between two dates
DAYS(end, start)
EDATE
Date representing a date a number of months before or after provided date
EDATE(date, numberOfMonths)
EOMONTH(date, numberOfMonths)
Date representing the last day of a month a provided number of months before or after provided date 
EOMONTH(date, numberOfMonths)
HOUR
The hours from a datetime or time value
HOUR(datetime)
ISOWEEKNUM
The ISO week number a date falls on
ISOWEEKNUM(date)
MINUTE
The minutes from a valid datetime or time value.
MINUTE(datetime)
MONTH
The month of a provided date in numeric format
MONTH(date)
SECOND
The seconds from a valid datetime or time value.
SECOND(datetime)
TIME
Converts a provided hour, minute and second to a fractional time.
TIME(hour, minute,second)
TIMEVALUE
Converts a time in the accepted formats to a valid fractional time.
Accepts:
"1:10 AM"
"1:10:05 AM"
"18:20"
"18:20:15"
TIMEVALUE(timeText)
WEEKNUM
The week number the date falls on
WEEKNUM(date)
YEAR
The year from a date
YEAR(date)
YEARFRAC
The number of years and fractional years between two dates
YEARFRAC(start,end)
Logic
Operator
Action
Example Usage
<
First is less than second
1 < 2
>
First is greater than second
1 > 2
=
First equals second
1 = 2
AND
Returns true if all arguments are true
AND(logicValue,logicValue,...)
FALSE
Returns the value of false
FALSE()
IF
Returns different value based on whether the first argument is true
IF(logicValue, trueValue, falseValue)
NOT
Returns the opposite of a logical value
NOT(TRUE()) 
returns FALSE()
OR
Returns true if any argument is true
OR(logicValue,logicValue,...)
TRUE
Returns the value of true
TRUE()
NULL
Returns the value of null
NULL()
ISNUMBER
Returns true if the argument is a number
ISNUMBER(value)
Math
Operator
Action
Usage Example
+
Adds the values to either side
10 + 2
-
Subtracts the values to either side
10 - 2
/
Divides the values to either side
10 / 2
*
Multiplies the values to either side
10 * 2
ABS
Absolute value
ABS(number)
CEILING
Rounds a number up to the closest multiple of significance
CEILING(number, significance)
DECIMAL
Converts text of a number to a decimal
DECIMAL(text)
EVEN
Rounds a number to nearest even integer
EVEN(number)
FACT
Factorial of a number
FACT(number)
FLOOR
Rounds a number down  to the closest multiple of significance
FLOOR(number,significance)
INT
Rounds a number to the nearest integer (less than or equal)
INT(number)
LOG
Logarithm of a number for a provided base
LOG(number, base)
MOD
Modulo of a number
MOD(number, divisor)
ODD
Rounds a number to nearest odd integer
ODD(number)
POWER
Raises a number to a power
POWER(number, power)
PRODUCT
Multiplies the arguments
PRODUCT(number, number2,...)
ROUND
Rounds a number to the closest value for a number of decimal places
ROUND(number, places)
ROUNDDOWN
Rounds a number down for a number of decimal places
ROUNDDOWN()ROUND(number, places)
ROUNDUP
Rounds a number up for a number of decimal places
ROUNDUP(number, places)
SIGN
-1 if number is negative
0 if zero
1 if positive 
SIGN(number)
SQRT
Positive square root of a positive number
SQRT(number)
SUM
Sums the arguments
SUM(number, number2,...)
TRUNC
Returns the integer component of a number
TRUNC(number)
Text
Operator
Action
Usage Example
&
Appends two strings to one another
"Hello " & "World"
CLEAN
Removes non printable characters from a string
CLEAN(text)
CONCATENATE
Appends two strings to one another
CONCATENATE(text1, text2)
EXACT
Checks if two strings are identical
EXACT(text1,text2)
FIND
Returns the first position a string occurs in text
FIND(searchQuery, text)
ISBLANK
Returns true if a string is blank
ISBLANK(text)
LEFT
Returns a substring from the beginning of a string
LEFT(text, numberOfCharacters)
LEN
Returns the length of a string
​
LOWER
Returns a string in lowercase
LOWER(text)
MID
Returns a substring from the middle of a string
MID(text, numberOfCharacters)
PROPER
Returns a string with words capitalized
PROPER(text)
REPLACE
Replaces part of a string with another provided string
REPLACE(text,startPosition, replacementLength, replacementText)
REPT
Repeats a string a number of times
REPT(text, numberOfTimes)
RIGHT
Returns a substring from the end of a string
RIGHT(text, numberOfCharacters)
SEARCH
Returns the first position in a string occurs in text after the start character position
SEARCH(searchQuery, text, startPosition)
SUBSTITUTE
Replaces all occurrences of a provided query with the provided replacement in a string
SUBSTITUTE(text, searchQuery, replacement)
TEXT
Converts a number into text according to a specified format
TEXT(number, format)
TRIM
Removes spaces from the beginning & end
TRIM(text)
UPPER
Returns a string in uppercase
UPPER(text)
​
​
Rollups
Next - Data Sources
External APIs
Last modified 1mo ago
Copy link
On this page
Creating a formula field
When do they calculate?
Output Types
Examples
Supported Operations