This is the full list of operators and functions you can use in a Formula field. Each entry shows how to write it and gives an example.
Here is the test: do you know what you want to work out, but not the function that does it? Read the short line under each name, or use your browser's find (Ctrl+F or Cmd+F) to jump to a name you know.
Example: a Vendors table in a vendor risk program. SUM adds up a risk score, DATEDIFF counts the days to a contract renewal, and IF turns a score into a review level. The section "Functions for a vendor risk table" shows all three at work.
The Advanced Editor of a Formula field, with the Fields, Operators and Functions tabs on the left and the Help panel on the right.
Plan Availability | All plan types. |
Permissions | Administrators and Solution Managers create and change formulas in the solutions they manage. |
Platform | Web |
Related Reading |
Finding a function in the editor
You do not need this page open to write a formula. The editor has the same list.
Open the Formula field's settings and turn on Advanced Editor at the bottom left.
On the left, select the Operators or Functions tab. Type in Search to narrow the list.
Select the ? beside a name. The Help panel on the right shows what it does, how to write it, and examples.
Select the name to put it in the formula where the cursor is.
The Functions tab shows an icon beside each name for its type: number, logic, text or date.
Tip: Field names go in square brackets, such as [Annual Contract Value], and text goes in double quotes, such as "Approved". Most errors come from a missing bracket or quote.
Functions for a vendor risk table
Three formulas cover most of what a risk register needs.
Add up a score. Inherent Risk Score totals the number behind each data type a vendor can see:
SUM(NUMERICVALUE([Data Types Accessed]))
Count days. Days Until Renewal counts from today to the renewal date. DATEDIFF subtracts the first date from the second:
DATEDIFF(TODAY(), [Contract Renewal Date], "days")
Pick a label. Review Level turns the score into a decision:
IF([Inherent Risk Score] >= 7, "Full assessment", IF([Inherent Risk Score] >= 3, "Standard review", "Questionnaire"))
Work across a link. Open Findings counts a vendor's linked findings that are still open:
COUNTIF([Findings].[Status] == "Open", [Findings])
Note: A function that reads a Linked Record field or a Multiple Select field gets a list, not one value. Wrap the list in SUM, COUNT, AVG, MAX or MIN to get one number.
Operators
The Operators tab in the Advanced Editor lists these ten. Each compares two values or does math with them.
ADD (+)
Adds two numbers, or joins two pieces of text.
Usage:
Number1 + Number2
Text1 + Text2
Examples:
[First Name] + [Last Name] → "First Name Last Name"
"One" + "Two" + "Three" → "OneTwoThree"
DIVIDE (/)
Divides two numbers, returning their quotient.
Usage:
Number1 / Number2
Examples:
6 / 2 = 3
[Numeric Field] / 2 → if [Numeric Field] is 8, returns 4
EQUALS (==)
Returns TRUE if compared values are equal and FALSE otherwise.
Usage:
Number1 == Number2
Text1 == Text2
Date1 == Date2
Examples:
1 == 2 → returns FALSE
[Status] == "Closed" → returns TRUE if status is exactly "Closed"
GREATER EQUAL (>=)
Returns TRUE if the first value is greater than or equal to the second, and FALSE otherwise.
Usage:
Value 1 >= Value 2
Examples:
1 >= 2 → returns FALSE
2 >= 2 → returns TRUE
3 >= 2 → returns TRUE
[Score] >= 10 → returns TRUE if the value of [Score] field is 10 or more
GREATER THAN (>)
Returns TRUE if the first value is greater than the second, and FALSE otherwise.
Usage:
Value 1 > Value 2
Examples:
1 > 2 → returns FALSE
[Score] > 10 → returns TRUE if the value of [Score] field is over 10
LESS EQUAL (<=)
Returns TRUE if the first value is less than or equal to the second, and FALSE otherwise.
Usage:
Value 1 <= Value 2
Examples:
1 <= 2 → returns TRUE
2 <= 2 → returns TRUE
3 <= 2 → returns FALSE
[Score] <= 10 → returns TRUE if the value of [Score] field is 10 or less
LESS THAN (<)
Returns TRUE if the first value is less than the second, and FALSE otherwise.
Usage:
Value 1 < Value 2
Examples:
1 < 2 → returns TRUE
[Score] < 10 → returns TRUE if the value of [Score] field is below 10
MULTIPLY (*)
Returns the product of two numbers.
Usage:
Number1 * Number2
Examples:
6 * 2 = 12
[Numeric Field] * 2 → if [Numeric Field] is 4, returns 8
NOT EQUAL (!=)
Returns TRUE if the two values are not equal, and FALSE otherwise.
Usage:
Value 1 != Value 2
Examples:
1 != 2 → returns TRUE
"Test" != "Test" → returns FALSE
[Rating] != "Poor" → returns TRUE if the value of [Rating] field is not exactly the text "Poor"
SUBTRACT (-)
Returns the difference between two numbers.
Usage:
Number1 - Number2
Examples:
6 - 2 = 4
[Numeric Field] - 2 → if [Numeric Field] is 4, returns 2
Functions A to Z
The Functions tab lists every function from A to Z, with an icon for its type. They are in the same order here.
ABS
Returns the absolute value of a number.
Usage:
ABS( Number )
Examples:
ABS(-2) = 2
ABS([Field1]) → if [Field1] is -25, result is 25
AND
Returns TRUE if all provided arguments are logically true, otherwise returns FALSE.
Usage:
AND( [Score] > 0, [Score] <= 10 )
AND( SUM([Invoice].[Total]) > 100, [Customer Tier] = "Premium" )
Examples:
IF( AND( [Has Support Contract] = "Yes", [Customer Tier] = "Premium"), "Premium Support", "Standard Support" ) → if [Has Support Contract] is "Yes" and [Customer Tier] is "Premium" then function displays "Premium Support" otherwise displays "Standard Support"
AND( [Status] = "Final", [Invoice].[Total] > 0 ) → if [Status] is "Final" and [Invoice].[Total] is 0 returns FALSE
ARRAYJOIN
Joins an array of values into a string with a separator.
Usage:
ARRAYJOIN( [Linked Record].[Assigned To] , Separator, Number of items to return )
Examples:
ARRAYJOIN( [Linked Record], ";" ) → returns a string of Linked Record Titles, separated by ";"
ARRAYJOIN( [Linked Record].[Assigned To], ";", 2 ) → returns a string of first two Assigned To values from a specified Linked Record, separated by ";"
ARRAYUNIQUE
Joins an array of values into a string with a separator, showing only unique values.
Usage:
ARRAYUNIQUE( [Linked Record].[Assigned To] , Separator, Number of items to return )
Example:
ARRAYUNIQUE( [Linked Record].[Assigned To], ";", 2 ) → returns a string of first two unique Assigned To values from a specified Linked Record, separated by ";"
AVG
Returns the average (arithmetic mean) of a series of numbers.
Usage:
AVG( Number1, Number2, Number3, … )
Examples:
AVG(2, 5, 7) = 4.67
AVG([Field1], [Field2]) → if [Field1] is 2 and [Field2] is 5 returns 3.5
AVGIF
Returns the average (arithmetic mean) of a series of numbers from a referenced field in a linked record that meet specified criteria.
Usage:
AVGIF( Criteria, [Linked Record Field], [Target Field] )
Example:
AVGIF( [Invoices].[Tax] > 0, [Invoices], [Tax] ) → if [Invoices] has 6 linked records and 3 of those have tax values greater than 0, will return the average of those 3 [Tax] values
BLANK
Returns a blank value. Does not require any arguments.
Usage:
BLANK()
Example:
IF([Price], [Price] + 10, BLANK()) → If the cell containing the Price is not empty, it adds 10 to the Price. If the cell is empty, the BLANK function ensures that the result is a blank value.
CASE
Returns a specified value when the first condition is met.
Usage:
CASE( Condition, Match1, Result1, Match2, Result2, ..., Default )
Example:
CASE( [Customer Tier], "Premium", "Premium Support", "Enhanced", "Enhanced Support", "Standard Support" ) → If [Customer Tier] is Premium function displays "Premium Support", if [Customer Tier] is Enhanced displays "Enhanced Support", otherwise displays the default of "Standard Support"
CEILING
Returns the rounded-up value of the specified number to the nearest multiple of the given significance.
Usage:
CEILING(Number, Significance)
Examples:
CEILING(7.8, 2) → returns 8. This example rounds up 7.8 to the nearest multiple of 2, resulting in 8.
CEILING(15.5, 5) → returns 20. This example rounds up 15.5 to the nearest multiple of 5, resulting in 20.
CEILING(-10, -3) → returns -9. This example rounds up -10 to the nearest multiple of -3, resulting in -9.
CHECKLIST_COUNT
Returns a count of checklist items that match a specified criteria.
Usage:
CHECKLIST_COUNT( [Checklist Field], Criteria )
Examples:
CHECKLIST_COUNT( [Checklist], [Checklist].[Completed] = True ) → Returns a count of completed Items in a given Checklist field. Note that True/False are boolean operators, not strings.
CHECKLIST_COUNT([Checklist], [Checklist].[Assignee] = "John Doe") → Returns a count of items assigned to John Doe.
CHECKLIST_COUNT([Checklist], [Checklist].[Due Date] > TODAY()) → Returns a count of items that are due after today.
CHECKLIST_COUNT([Checklist], [Checklist].[Due Date] < TODAY() AND [Checklist].[Completed] = False) → Returns a count of items that are due or before today and have not been completed.
CONCAT
Combines multiple text values into one.
Usage:
CONCAT( Text1, Text2, ... )
Examples:
CONCAT( "This", "is", "a", "test" ) → returns "Thisisatest" as text
CONCAT( RECORD_ID(), "-", [Name] ) → if [Name] is Test returns "00000-Test" (where 00000 is the record id)
CONTAINS
Returns TRUE if expression contains the defined text and FALSE otherwise.
Usage:
CONTAINS( Expression, Text )
Examples:
CONTAINS( [Owner], "Maria" ) → if [Owner] field contains the text "Maria" then function returns TRUE
OR( CONTAINS( [Owner], "Maria" ), CONTAINS( [Owner], "Lee" ) ) → if [Owner] field contains either the text "Maria" OR the text "Lee" then function returns TRUE
COUNT
Returns the count of items in a referenced field, or the count of records in a linked record field.
Usage:
COUNT( [Linked Record Field] )
COUNT( [Linked Record Field].[Field] )
COUNT( [Multiple Select Field] )
Examples:
COUNT( [Invoices] ) → if [Invoices] has 6 linked records, returns 6
COUNT( [Invoices].[SKU] ) → if there are a total of 3 unique [SKU] appearing 12 times across all linked [Invoices], returns 12
COUNT( [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections, returns 2
COUNTA
Count the number of non-empty values. This function counts both numeric and text values.
Usage:
COUNTA( TextOrNumber1, TextOrNumber2, ... )
Example:
COUNTA([Linked Record].[Number], [Linked Record].[Text]) → Counts the non-empty values in specified fields of a Linked Record. In case if Linked Record field has multiple records linked, each non-empty [Linked Record].[Number] and [Linked Record].[Text] will be counted separately.
COUNTALL
Count the number of all elements including text and blanks. This function counts both numeric and text values.
Usage:
COUNTALL( TextOrNumber1, TextOrNumber2, ... )
Examples:
COUNTALL(1, 2, 3, "", "four") → returns 5 Counts the number of all elements including text and blanks.
COUNTALL([Linked Record].[Number], [Linked Record].[Text]) → Counts the number of blank and non-blank values in specified fields of a Linked Record. In case if Linked Record field has multiple records linked, each non-empty [Linked Record].[Number] and [Linked Record].[Text] will be counted separately.
COUNTIF
Counts the items in a field, or the records in a Linked Record field, that match a condition.
Usage:
COUNTIF( Criteria, [Linked Record Field] )
COUNTIF( Criteria, [Multiple Select Field] )
Examples:
COUNTIF( [Invoices].[Status] == "Open", [Invoices] ) → if [Invoices] has 6 linked record and 4 have a [Status] value of "Open", returns 4
COUNTIF( [Has Impact] == "Yes", [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections and [Has Impact] is "Yes", returns 2
COUNTIF_DISTINCT
Counts the different items in a field, or the different records in a Linked Record field, that match a condition.
Usage:
COUNTIF_DISTINCT( Criteria, [Linked Record Field] )
COUNTIF_DISTINCT( Criteria, [Linked Record Field].[Field] )
COUNTIF_DISTINCT( Criteria, [Multiple Select Field] )
Examples:
COUNTIF_DISTINCT( [Trouble Tickets].[Asset Type] == "Laptop", [Trouble Tickets] ) → if 12 [Trouble Tickets] out of 20 have an [Asset Type] of "Laptop" returns 12
COUNTIF_DISTINCT( [Trouble Tickets].[Asset Type] == "Laptop", [Trouble Tickets].[Asset] ) → if 12 [Trouble Tickets] out of 20 have an [Asset Type] of "Laptop" and there are 5 unique assets, returns 5
COUNTIF_DISTINCT( [Has Impact] == "Yes", [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections and [Has Impact] is "Yes", returns 2 - if [Impacted Areas] multiple select list has 2 selections and [Has Impact] is "No", returns 0
COUNT_DISTINCT
Returns the count of distinct (unique) items in a referenced field, or the count of distinct records in a linked record field.
Usage:
COUNT_DISTINCT( [Linked Record Field] )
COUNT_DISTINCT( [Linked Record Field].[Field] )
COUNT_DISTINCT( [Multiple Select Field] )
Examples:
COUNT_DISTINCT( [Contacts] ) → if [Contacts] has 3 linked records, returns 3
COUNT_DISTINCT( [Invoices].[SKU] ) → if there are a total of 3 unique [SKU] appearing 12 times across all linked [Invoices], returns 3
COUNT_DISTINCT( [Impacted Areas] ) → if [Impacted Areas] multiple select list has 2 selections, returns 2
DATE
Converts a string to a date value.
Usage:
DATE( String )
Examples:
DATE( "1 Jan 2021" ) → returns 1/1/2021 as a date value
IF( IS_NOT_NULL( [Target Date] ), DATEADD( [Start Date], 30, "days" ), DATE( [Target Date] ) ) → if [Target Date] text field is empty returns a date 30 days after [Start Date], otherwise returns contents of [Target Date] as a date value
DATEADD
Adds a specified number of hours, days, weeks, months or years to a date value.
Usage:
DATEADD( Date, Count, Unit ) Unit is one of the following: ∙ minutes ∙ hours ∙ days ∙ weeks ∙ months ∙ years
Examples:
DATEADD( [Target Date], 10, "days" ) → if [Target Date] is 1/1/2021 returns 1/11/2021 as a date value
IF( IS_NOT_NULL( [Due Date] ), DATEADD( [Start Date], 10, "days" ), DATE( [Due Date] ) ) → if [Due Date] text field is empty returns a date 10 days after [Start Date], otherwise returns contents of [Due Date] as a date value
DATEDIFF
Returns the difference between two dates as a number representing specified date / time units.
Usage:
DATEDIFF( Date1, Date2, Unit ) Unit is one of the following: ∙ minutes ∙ hours ∙ days ∙ weeks ∙ months (uses 30 days for calculation) ∙ years
Examples:
DATEDIFF( "01-01-2020", "01-02-2020", "days" ) → returns the number 1
DATEDIFF( "01-01-2020", "01-02-2020", "weeks" ) → returns the number 0.14
DATE_FORMAT
Returns a date formatted in the specified way (in a string).
Usage:
DATE_FORMAT( Date, Format ) Format is one of the following: ∙ compact - 4/14/2021 ∙ standard - Apr 14, 2021 ∙ long - Sun, Apr 14, 2021
Examples:
DATE_FORMAT( "01-01-2020", "compact" ) → returns 1/1/2020
DATE_FORMAT( "01-01-2020", "standard" ) → returns Jan 1, 2020
DATE_FORMAT( "01-01-2020", "long" ) → returns Weds, Jan 1, 2020
DATE(DATE_FORMAT( "01-01-2020", "long" )) → returns Weds, Jan 1, 2022 as a date vs. string
DAY
Returns the numeric day of the month from a date expression.
Usage:
DAY( Date )
Examples:
DAY( "01-01-2020" ) → returns 1
DAY( "01-21-2020" ) → returns 21
ENCODE_URL_COMPONENT
Returns the text with special characters encoded, so it can be used in a web address. The characters - _ . ~ are not encoded.
Usage:
ENCODE_URL_COMPONENT(component_string)
Examples:
ENCODE_URL_COMPONENT("chicken & waffles") → returns "chicken%20%26%20waffles" This example encodes the string "chicken & waffles" for use in a URL, replacing the space with %20 and the ampersand with %26.
ENCODE_URL_COMPONENT("hello_world") → returns "hello_world" The function leaves the string "hello_world" unchanged as the characters - _ . ~ are not encoded.
EVEN
Rounds a number up to the nearest even integer.
Usage:
EVEN( Number )
Examples:
EVEN(3) → returns 4. This example rounds 3 up to the nearest even integer, resulting in 4.
EVEN(5.7) → returns 6. This example rounds 5.7 up to the nearest even integer, resulting in 6.
EVEN(-3) → returns -2. This example rounds -3 up to the nearest even integer, resulting in -2.
EXP
Calculates the exponential value of a given number. Precision is optional and defaults to 2.
Usage:
EXP(Number, Precision)
Examples:
EXP(1) → returns 2.72 This example calculates 'e' raised to the power of 1, resulting in the approximate value of 2.72.
EXP(0) → returns 1 This example calculates 'e' raised to the power of 0, resulting in 1.00
EXP(-2,3) → returns 0.135 This example calculates 'e' raised to the power of -2, resulting in the approximate value of 0.135 with a precision of three decimal places
FIND
Returns the position of the first match of the text, counting from 0. Returns -1 if there is no match. Start Position sets where the search starts (0 if left out). End Position sets where it ends (the end of the text if left out).
Usage:
FIND(String to Find, Where to Search, Start Position, End Position)
Examples:
FIND("sweet", "Home, sweet home") → returns the number 6
FIND("home", "Home, sweet home", 5) → returns the number 12
FIND("e", "Home, sweet home", 6, 12) → returns the number 8
FIND("x", "Home, sweet home") → returns the negative number -1
FIRST
Returns the first value from a list.
Usage:
FIRST( Expression )
Example:
FIRST( [Categories] ) → if [Categories] is a multi-select list with selected values Value1, Value2 and Value3, returns "Value1" as text
FLOOR
Returns the rounded-down value of the specified number to the nearest multiple of the given significance.
Usage:
FLOOR(Number, Significance)
Examples:
FLOOR(7.8, 2) → returns 6. This example rounds down 7.8 to the nearest multiple of 2, resulting in 6.
FLOOR(15.5, 5) → returns 15. This example rounds down 15.5 to the nearest multiple of 5, resulting in 15.
FLOOR(-10, -3) → returns -12. This example rounds down -10 to the nearest multiple of -3, resulting in -12.
GET_LIST
Sorts and filters related records and returns a list of values of a specified field from those related records.
Usage:
GET_LIST( [Related Record].[Filter Condition], [Related Record].[Field to Return], [Related Record].[Field to Sort By], [Sorting Order])
Examples:
ARRAYJOIN(GET_LIST([LR].[Status] = "Complete", [LR].[Title], [LR].[Number], "ASC"), "; ") → Creates a list of Titles values from records that are of "Complete" status, sorted by the Number field. Values of the list are separated with a ";".
SUM(TOP(GET_LIST([Sub-Items].[Number] > 100, [Sub-items].[Number], [Sub-Items].[Date]), 3)) → Returns the sum of Numbers the three most recent Sub-Items that have Number greater than 100.
ARRAYJOIN(TOP(GET_LIST([Checklist].[Due Date] < TODAY(), [Checklist].[Assignee], [Checklist].[Due Date], "DESC"), 3)) → Returns a list of names of members assigned to items with Due Date in the past. The list is limited to three most recent items.
HOUR
Returns the numeric hour of the day from a datetime expression.
Usage:
HOUR( Date )
Examples:
HOUR( "01-01-2020 01:10:11" ) → returns 1
HOUR( "01-21-2020 02:11:12" ) → returns 2
HOUR( "01-21-2020 13:11:12" ) → returns 13
IF
Returns Value 1 if a logical expression is "TRUE", and Value 2 if it is "FALSE".
Usage:
IF( Expression, Value 1, Value 2 )
Examples:
IF( [Percent Complete] == 100, "Completed", "In Progress" ) → displays "Completed" if [Percent Complete] equals 100 and otherwise displays "In Progress"
IF( [Status] == "Approved", "Closed", IF( [Status] == "Pending Approval", "Review", "In Progress" ) ) → displays "Closed" if [Status] is "Approved", "Review" if it is "Pending Approval", and "In Progress" otherwise
IFERROR
Returns a specified value if the calculation generates an error.
Usage:
IFERROR( Expression, Error Value )
Examples:
IFERROR( 1 / 0, "Cannot divide by zero" ) → returns the text "Cannot divide by zero"
IFERROR( [Price] * [Tax], 0 ) → returns 0 if there is an error in the calculation
IFNONE
Returns a specified value if evaluated expression is null or empty.
Usage:
IFNONE( Expression, Value )
Example:
IFNONE( [Owner], "No Owner" ) → displays "No Owner" if [Owner] field is empty
IS_NOT_NULL
Returns TRUE if expression is not null or empty and FALSE otherwise.
Usage:
IS_NOT_NULL( Expression )
Example:
IF( IS_NOT_NULL( [Owner] ), "Owner is " + [Owner], "No Owner" ) → displays "No Owner" if [Owner] field is empty, and "Owner is" [Owner] otherwise
LAST_MODIFIED_BY
Returns a name of a member who last modified a value of a specified field.
Usage:
LAST_MODIFIED_BY([Field name])
Examples:
LAST_MODIFIED_BY([Status]) → returns a name of a member who modified a value of a Status field last
LAST_MODIFIED_BY([Status], [Priority]) → returns a name of a member who was the last to modify values of Status and Priority fields
LAST_MODIFIED_TIME
Returns a date and a time when a value of the specified field was last changed.
Usage:
LAST_MODIFIED_TIME([Field name])
Examples:
LAST_MODIFIED_TIME([Status]) → returns the date and time when a value of the Status field was last changed
LAST_MODIFIED_TIME([Status], [Priority]) → returns the latest modification date/time value for the Status and Priority fields
LEFT
Returns the first n characters from specified text.
Usage:
LEFT( Text, Characters )
Examples:
LEFT( "Testing", 4 ) → returns "Test" as text
LEFT( ("This is " + "a test"), 9 ) → returns "This is a" as text
LENGTH
Returns the numeric character count of specified text.
Usage:
LENGTH( Text )
Examples:
LENGTH( "Testing" ) → returns 7
LENGTH( "This is " + "a test" ) → returns 14
LIKE
Returns TRUE if expression contains specified pattern.
Usage:
LIKE( Expression, Pattern )
Examples:
LIKE( [Owner], "J*" ) → returns TRUE if [Owner] field contains a string starting with the letter J
LIKE( [Assigned To], "Ol?g" ) → returns TRUE if [Assigned To] field contains a string starting with "Ol" and ending in "g" with any character in between
LOG
Returns the logarithm of the specified number with the specified base.
Usage:
LOG(Number, Base, Precision)
Examples:
LOG(8) → returns 2.07 This example calculates the natural logarithm (base 'e') of 8 using default precision (2), resulting in the approximate value of 2.07.
LOG(100, 10) → returns 2 This example calculates the logarithm of 100 with a base of 10, resulting in 2.
LOWER
Returns specified text in lower case.
Usage:
LOWER( Text )
Examples:
LOWER( "Testing" ) → returns "testing"
LOWER( "This is " + "a Test" ) → returns "this is a test"
MAX
Returns the maximum numeric value from a series of numbers.
Usage:
MAX( [Field1], [Field2], ... )
MAX( [Linked Record Field].[Field] )
Examples:
MAX( [Invoices].[Total] ) → if [Invoices] has 6 linked records with the highest [Total] value of 10, returns 10
MAX( [Count A], [Count B] ) → if [Count A] is 3 and [Count B] is 4, returns 4
MEDIAN
Returns the median ("midpoint") value of a series of numbers.
Usage:
MEDIAN( [Number1], [Number2], ... )
MEDIAN( [Linked Record Field].[Number1] )
Examples:
MEDIAN( [Invoices].[Total] ) → if [Invoices] has 4 linked records with [Total] values of 1, 2, 3 and 4, returns 2.5
MEDIAN( [Val1], [Val2], [Val3] ) → if [Val1] is 1 and [Val2] is 2 and [Val3] is 3, returns 2
MID
Extracts a substring from a text string based on specified start and length parameters.
Usage:
MID(Where to Search, Start Position, Length)
Example:
MID("Home, sweet home", 6, 5) → returns "sweet"
MIN
Returns the minimum numeric value from a series of numbers.
Usage:
MIN( [Number1], [Number2], ... )
MIN( [Linked Record Field].[Number] )
Examples:
MIN( [Invoices].[Total] ) → if [Invoices] has 6 linked records with the lowest [Total] value of 10, returns 10
MIN( [Count A], [Count B] ) → if [Count A] is 3 and [Count B] is 4, returns 3
MINUTE
Returns the numeric minute from a datetime expression.
Usage:
MINUTE( Date )
Examples:
MINUTE( "01-01-2020 01:10:11" ) → returns 10
MINUTE( "01-21-2020 02:11:12" ) → returns 11
MINUTE( "01-21-2020 13:47:12" ) → returns 47
MOD
Returns the remainder of a division operation.
Usage:
MOD(Dividend, Divisor)
Examples:
MOD(10, 3) → returns 1
MOD(15, 7) → returns 1
MOD(8, 2) → returns 0
MONTH
Returns the numeric month from a datetime expression.
Usage:
MONTH( Date )
Examples:
MONTH( "01-01-2020 01:10:11" ) → returns 1
MONTH( "11-21-2020 02:11:12" ) → returns 11
NOT
Returns TRUE if expression is logically false and FALSE otherwise.
Usage:
NOT( Expression )
Examples:
IF( NOT( [Status] == "Closed" ), [Status], "Case is Closed" ) → displays "Case is Closed" if [Status] is closed, otherwise displays the value of [Status]
NOT( 2 == 1 ) → returns TRUE
NOT( 1 == 1 ) → returns FALSE
NOW
Returns the current date and time as a datetime valueIf you only need the current date, you can use TODAY() instead.
Usage:
NOW()
Example:
NOW() → returns today's date and the current time as a datetime value
NUMBER
Returns a referenced string as a number.
Usage:
NUMBER( Expression, Precision )
Examples:
NUMBER( "12.351" ) → returns 12.35 as a number
NUMBER( [Text], 4 ) → if [Text] is "12.35143" returns 12.3514 as a number
NUMERICVALUE
Returns the number behind the selected choice of a Single Select field, or 0 if nothing is selected. On a Multiple Select field it returns a list, one number per selected choice, so wrap it in SUM.
Usage:
NUMERICVALUE( [Single or Multi-Select Field] )
Examples:
NUMERICVALUE([Score]) = 3
SUM(NUMERICVALUE([Categories])) → if [Categories] has two selections with the values 2 and 3, returns 5
ODD
Returns the nearest odd integer greater than or equal to the specified number.
Usage:
ODD( Number )
Examples:
ODD(3) → returns 3. This example rounds 3 to the nearest odd integer, resulting in 3.
ODD(5.7) → returns 7. This example rounds 5.7 up to the nearest odd integer, resulting in 7.
ODD(-2) → returns -1. This example rounds -2 up to the nearest odd integer, resulting in -1.
OR
Returns TRUE if any of the specified expressions are logically true and FALSE otherwise.
Usage:
OR( Expression1, Expression2, ... )
Examples:
OR( 2 == 1, 1 == 1 ) → returns TRUE
OR( [Status] == "Closed", [Status] == "Canceled" ) → returns TRUE if Status is either Closed or Canceled
POWER
Calculates the result of raising a number to a specified power. Precision is optional and defaults to 2.
Usage:
POWER(Base, Exponent, Precision)
Examples:
POWER(2, 3) → returns 8 This example calculates 2 raised to the power of 3, resulting in 8.
POWER(4, 0.5) → returns 2 This example calculates the square root of 4 by raising it to the power of 0.5, resulting in 2.
POWER(3, -2, 4) → returns 0.1111 This example calculates 3 raised to the power of -2, which is equivalent to 1 divided by (3^2), resulting in 0.1111
QUARTER
Returns the numeric quarter from a datetime expression.
Usage:
QUARTER( Date )
Examples:
QUARTER( "01-01-2020 01:10:11" ) → returns 1
QUARTER( "11-21-2020 02:11:12" ) → returns 4
RECORD_ID
Returns the unique record id of the current record as text.
Usage:
RECORD_ID()
Example:
RECORD_ID() → returns the current record id
RELATED_RECORDS_SORT
Sorts related records and returns a list of values of a specified field from those linked records.
Usage:
RELATED_RECORDS_SORT( [Related Record].[Field to Return], [Related Record].[Field to Sort By], [Sorting Order])
Examples:
ARRAYJOIN( RELATED_RECORDS_SORT([Linked Record].[Title], [Linked Record].[Number], "ASC"), "; ") → Creates a list of linked Titles, separated with a "; " and sorted by the linked Number field.
SUM(TOP(RELATED_RECORDS_SORT([Sub-Items].[Number], [Sub-items].[Date]), 3)) → Returns the sum of the three most recent Number fields from the Sub-Items.
TEXT(TOP(RELATED_RECORDS_SORT([Checklist].[Assignee], [Checklist].[Due Date], "DESC"), 1)) → Returns the name of person assigned to an item with the latest Due Date in a Checklist field
RELATED_RECORD_SORT
Sorts related records and returns a value of a specified field of a first record.
Usage:
RELATED_RECORD_SORT( [Related Record].[Field to Return], [Related Record].[Field to Sort By], [Sorting Order])
Examples:
RELATED_RECORD_SORT([Linked Record].[Title], [Linked Record].[Number], "ASC") → sorts Linked Records by the Number field and returns the value of the Title from the record with the lowest Number value.
RELATED_RECORD_SORT([Sub-Items].[Number], [Sub-items].[Date]) → sorts Sub-items by the Date field and returns the value of the Number field from the item with the latest Date
RELATED_RECORD_SORT([Checklist].[Assignee], [Checklist].[Due Date]) → sorts Checklist items by the Due Date field and returns the value of the Assignee field from the item with the latest Due Date
RELATED_RECORD_SORT([Linked Record].[Multiple Select], [Linked Record].[Number]) → sorts Linked Records by the Number field and returns the coma separated string of values of the Multiple Select field from the record with the lowest Number value
RELATED_RECORD_SORT([Lookup], [Lookup], "DESC") → sorts Lookup values and returns the highest value.
REPLACE
Replaces specified characters with another set of characters, returning the modified text.
Usage:
REPLACE( Text, Text to Replace, Replace with Text )
Examples:
REPLACE( "Testing", "s", "x" ) → returns "Texting"
REPLACE( "This is a test", "test", "text" ) → returns "This is a text"
REPLACE( "yes yes yes", "yes", "no" ) → returns "no no no"
REPT
Returns a new text string by repeating the specified text string the specified number of times.
Usage:
REPT(Text, Number)
Examples:
REPT("abc ", 3) → returns "abc abc abc ". This example repeats the text string "abc " three times.
REPT("123", 5) → returns "123123123123123". This example repeats the text string "123" five times.
REPT("hello", 0) → returns "". This example, with a repeat count of 0, results in an empty string.
RFIND
Returns a number that is the offset of the matched string's last instance. If no match is found, returns -1.
Usage:
RFIND(String to Find, Where to Search, Start Position, End Position)
Examples:
RFIND("home", "Home, sweet home") → returns 12
RFIND("e", "Home, sweet home", 5, 10) → returns 9
RFIND("x", "Home, sweet home") → returns -1
RIGHT
Returns the last n characters from specified text.
Usage:
RIGHT( Text, Characters )
Examples:
RIGHT( "Testing", 4 ) → returns "ting" as text
RIGHT( ("This is " + "a test"), 9 ) → returns "is a test" as text
ROUND
Rounds a number to the specified number of decimal places.
Usage:
ROUND( Number, Digits )
Examples:
ROUND( [Total], 0 ) → if [Total] is 19.95, function returns 20
ROUND( [Total], 1 ) → if [Total] is 12.45, function returns 12.5
ROUNDDOWN
Returns the rounded-down value of the specified number to the specified number of digits.
Usage:
ROUNDDOWN(Number, Digits)
Examples:
ROUNDDOWN(5.8, 0) → returns 5. This example rounds down 5.8 to the nearest integer.
ROUNDDOWN(7.35, 1) → returns 7.3. This example rounds down 7.35 to one decimal place, resulting in 7.3.
ROUNDDOWN(9876, -2) → returns 9800. This example rounds down 9876 to the nearest hundred, resulting in 9800.
ROUNDUP
Returns the rounded-up value of the specified number to the specified number of digits.
Usage:
ROUNDUP(Number, Digits)
Examples:
ROUNDUP(5.3, 0) → returns 6. This example rounds up 5.3 to the nearest integer.
ROUNDUP(7.85, 1) → returns 7.9. This example rounds up 7.85 to one decimal place, resulting in 7.9.
ROUNDUP(1234, -2) → returns 1300. This example rounds up 1234 to the nearest hundred, resulting in 1300.
SQRT
Calculates the square root of a given number with specified precision.
Usage:
SQRT(Number, Precision)
Examples:
SQRT(9) → returns 3.00
SQRT(16, 3) → returns 4.000
SQRT(2, 3) → returns 1.414
STDEV
Returns the standard deviation of a series of numbers.
Usage:
STDEV( Number1, Number2, ... )
STDEV( [Linked Record].[Field] )
Examples:
STDEV( [Number1], [Number2], [Number3] ) → if [Number1] is 10, [Number2] is 20 and [Number3] is 30, function returns 10
STDEV( [Invoice].[Total] ) → if 3 [Invoices] have [Total] values of 100, 110 and 150, function returns 26.46
SUM
Returns the sum of a series of numbers.
Usage:
SUM( Number1, Number2, ... )
SUM( [Linked Record].[Field] )
Examples:
SUM( [Number1], [Number2], [Number3] ) → if [Number1] is 10, [Number2] is 20 and [Number3] is 30, function returns 60
SUM( [Invoice].[Total] ) → if 3 [Invoices] have [Total] values of 100, 110 and 150, function returns 360
SUMIF
Returns the sum of a series of numbers that match specified criteria.
Usage:
SUMIF( Criteria, [Linked Record].[Number1] )
Example:
SUMIF( [Invoice].[Status] == "Final", [Invoice].[Total] ) → if 3 [Invoices] have [Total] values of 100, 110 and 150, and [Invoice].[Status] set to "Final" function returns 360
T
Returns the argument if it is text and blank otherwise.
Usage:
T(Value)
Examples:
T("hello") → returns "hello". This example returns the text "hello" because it is a text value.
T(42) → returns "". This example returns an empty string because the value 42 is not text.
T(True) → returns "". This example returns an empty string because the boolean value True is not text.
TEXT
Returns the referenced numeric or date expression as text.
Usage:
TEXT( Expression )
Examples:
TEXT( [Due Date] ) → if [Due Date] is 12/31/2021 returns "12/31/2021" as text
TEXT( [Score] ) → if [Score] is 10 returns "10" as text
TODAY
Returns the current date as a date value.
Usage:
TODAY()
Example:
TODAY() → returns today's date as a date value
TOP
Accepts an array and limits it to a specific number of values. Both arguments are required.
Usage:
TOP([Array of values], [Number of values to return])
Examples:
ARRAYJOIN(TOP([Linked Record].[Title], 2), ";" ) → returns a list of the first two Titles from a Linked Record.
SUM(TOP([Sub-Items].[Number], 3)) → sums up the Number fields of the first three Sub-Items.
SUM((TOP(RELATED_RECORDS_SORT([Linked Record].[Number], [Linked Record].[Date]), 3)) → Returns the sum of the three most recent Number fields from the Linked Record.
TRIM
Removes whitespace from the beginning and end of the specified text.
Usage:
TRIM( Text )
Examples:
TRIM( " Testing " ) → returns "Testing"
TRIM( "This is " + "a Test " ) → returns "This is a test"
UPPER
Returns specified text in upper case.
Usage:
UPPER( Text )
Examples:
UPPER( "Testing" ) → returns "TESTING"
UPPER( "This is " + "a Test" ) → returns "THIS IS A TEST"
WEEK
Returns the numeric week number of the year from a datetime expression.
Usage:
WEEK( Date )
Examples:
WEEK( "01-01-2020 01:10:11" ) → returns 1
WEEK( "11-21-2020 02:11:12" ) → returns 47
WEEKDAY
Returns the day of the week as a number from 1 to 7. StartDayOfWeek is optional and can be "Monday" or "Sunday". Weeks start on Monday if you leave it out.
Usage:
WEEKDAY(Date, StartDayOfWeek)
Examples:
WEEKDAY( TODAY() ) → returns 3, assuming today is Wednesday.
WEEKDAY( TODAY(), "Sunday") → returns 4, assuming today is Wednesday.
WORKDAYS
Returns the date a given number of working days after the start date. Working days and holidays come from your SmartSuite profile.
Usage:
WORKDAYS( Start Date, Days )
Example:
WORKDAYS( "4/2/2020", 5 ) → returns date 4/9/2020
WORKDAYS_DIFF
Returns the number of working days between a start date and an end date, counting both. Working days and holidays come from your SmartSuite profile.
Usage:
WORKDAYS_DIFF( Start Date, End Date )
Example:
WORKDAYS_DIFF( "4/2/2020", "4/7/2020" ) → returns 4 as a number
YEAR
Returns the numeric year from a datetime expression.
Usage:
YEAR( Date )
Examples:
YEAR( "01-01-2020 01:10:11" ) → returns 2020
YEAR( "11-21-2019 02:11:12" ) → returns 2019
Frequently asked questions
How do I add days to a date?
How do I add days to a date?
Use DATEADD( Date, Count, Unit ), such as DATEADD([Due Date], 180, "days"). The unit is minutes, hours, days, weeks, months or years. A negative number gives an earlier date.
How do I get the hours between a start time and an end time?
How do I get the hours between a start time and an end time?
Use DATEDIFF with "hours" as the unit: DATEDIFF([Time In], [Time Out], "hours").
How do I count working days between two dates?
How do I count working days between two dates?
Use WORKDAYS_DIFF( Start Date, End Date ). Working days and holidays come from your SmartSuite profile.
How do I remove extra spaces from a text value?
How do I remove extra spaces from a text value?
TRIM( Text ) removes the spaces at the start and end of the text.
How do I total a number across linked records?
How do I total a number across linked records?
SUM([Linked Record].[Number]) adds every linked value. SUMIF adds only the ones that match a condition.
Last verified 31 Oct 2026.