This is the complete list of operators and functions you can use in a SmartSuite Formula field, with the usage and an example for each. Functions are listed alphabetically after the operators; use your browser's find (Ctrl+F or Cmd+F) to jump to a name.
Plan Availability | All plan types |
Permissions | Administrators & Solution Managers - Can create and modify formulas in solutions they manage. |
Platform | Web |
Related Reading |
Where do I start?
For hands-on practice, open the Formula Operators & Functions solution template, which includes:
Predefined basic formulas.
Interactive examples to modify and adapt.
Guidance to create complex custom formulas.
For the Formula field itself, read the Formula Field Article, which includes:
Video tutorials on writing complex formulas.
A step-by-step guide to mastering Formula fields.
Operators
-(SUBTRACT) Return the difference between two numbers
Usage:
Number1 - Number2
Example:
6 - 2 = 4[Numeric Field] - 2 → if [Numeric Field] is 4, returns 2
!=(NOT EQUAL) Returns TRUE if left (first) value is not equal to the right (second) value and FALSE otherwise
Usage:
Value 1 != Value 2
Example:
1 != 2 → returns TRUE ”Test" != "Test"→ returns FALSE [Rating] != "Poor" → returns TRUE if the value of [Rating] field is not exactly the text "Poor”
*(MULTIPLY) Return the the product of two numbers
Usage:
Number1 * Number2
Example:
6 * 2 = 12 [Numeric Field] * 2 → if [Numeric Field] is 4, returns 8
/(DIVIDE) Divides two numbers, returning their quotient
Usage:
Number1 / Number2
Example:
6 / 2 = 3 [Numeric Field] / 2 → if [Numeric Field] is 8, returns 4
+(ADD) Add together two numbers or concatenate text values
Usage:
Number1 + Number2 Text1 + Text2
Example:
[First Name] + [Last Name] → "First Name Last Name" "One" + "Two" + "Three" → "OneTwoThree”
<(LESS THAN) Returns TRUE if left (first) value is less than right (second) value and FALSE otherwise
Usage:
Value 1 < Value 2
Example:
1 < 2 → returns TRUE [Score] < 10 → returns TRUE if the value of [Score] field is below 10
<=(LESS THAN OR EQUAL TO) Returns TRUE if left (first) value is less than or exactly equal to right (second) value and FALSE otherwise
Usage:
Value 1 <= Value 2
Example:
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
==(EQUALS) Returns TRUE if compared values are equal and FALSE otherwise
Usage:
Number1 == Number2 Text1 == Text2 Date1 == Date2
Example:
1 == 2 → returns FALSE [Status] == "Closed" → returns TRUE if status is exactly "Closed”
>(GREATER THAN) Returns TRUE if left (first) value is greater than right (second) value and FALSE otherwise
Usage:
Value 1 > Value 2
Example:
1 > 2 → returns FALSE [Score] > 10 → returns TRUE if the value of [Score] field is over 10
>=(GREATER THAN OR EQUAL TO) Returns TRUE if left (first) value is greater than or exactly equal to right (second) value 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
Functions
ABSReturns the absolute value of a number
Usage:
ABS( Number )
Example:
ABS(-2) = 2 ABS([Field1]) → if [Field1] is -25, result is 25
ANDReturns 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
ARRAYJOINJoins an array of values into a string with a separator
Usage:
ARRAYJOIN( [Linked Record].[Assigned To] , Separator, Number of items to return )
Example:
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 “;“
ARRAYUNIQUEJoins 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 “;“
AVGReturns the average (arithmetic mean) of a series of numbers
Usage:
AVG( Number1, Number2, Number3, … )
Example:
AVG(2, 5, 7) = 4.67 AVG([Field1], [Field2]) → if [Field1] is 2 and [Field2] is 5 returns 3.5
AVGIFReturns 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
BLANKReturns 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.
CASEReturns 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"
CEILINGReturns 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_COUNTReturns 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.
CONCATCombines 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)
CONTAINSReturns TRUE if expression contains the defined text and FALSE otherwise
Usage:
CONTAINS( Expression, Text )
Examples:
CONTAINS( [Owner], "Jon" ) → if [Owner] field contains the text "Jon" then function returns TRUE
OR( CONTAINS( [Owner], "Jon" ), CONTAINS( [Owner], "Peter" ) ) → if [Owner] field contains either the text "Jon" OR the text "Peter" then function returns TRUE
COUNTReturns 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
COUNT_DISTINCTReturns 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
COUNTACount 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.
COUNTALLCount the number of all elements including text and blanks. This function counts both numeric and text values.
Usage:
COUNTALL( TextOrNumber1, TextOrNumber2, ... )
Example:
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.
COUNTIFReturns a count of items in a referenced field, or the count of records in a linked record field that match specified criteria
Usage:
COUNTIF( Criteria, [Linked Record Field] ) COUNTIF( Criteria, [Multiple Select Field] )
Example:
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_DISTINCTReturns a count of distinct (unique) items in a referenced field, or the count of distinct records in a linked record field that match specified criteria
Usage:
COUNTIF_DISTINCT( Criteria, [Linked Record Field] ) COUNTIF_DISTINCT( Criteria, [Linked Record Field].[Field] ) COUNTIF_DISTINCT( Criteria, [Multiple Select Field] )
Example:
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
DATEConverts a string to a date value
Usage:
DATE( String )
Example:
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
DATE_FORMATReturns 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
Example:
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
DATEADDAdds 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
Example:
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
DATEDIFFReturns 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
Example:
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
DAYReturns the numeric day of the month from a date expression
Usage:
DAY( Date )
Example:
DAY( "01-01-2020" ) → returns 1 DAY( "01-21-2020" ) → returns 21
ENCODE_URL_COMPONENTReturns the input string with certain characters replaced by their encoded equivalents, making it suitable for constructing URLs or URIs. The characters - _ . ~ are not encoded.
Usage:
ENCODE_URL_COMPONENT(component_string)
Example:
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.
EVENRounds a number up to the nearest even integer.
Usage:
EVEN( Number )
Example:
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 -2 up to the nearest even integer, resulting in -2.
EXPCalculates the exponential value of a given number. Precision is optional and defaults to 2.
Usage:
EXP(Number, Precision)
Example:
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
FINDReturns a number that is the offset of the matched string’s first instance. If no match is found, returns -1Start Position defines where to start the search (defaults to 0 if not specified). End Position defines where to end the search (defaults to the end of the string if not specified).
Usage:
FIND(String to Find, Where to Search, Start Position, End Position)
Example:
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
FIRSTReturns 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
FLOORReturns the rounded-down value of the specified number to the nearest multiple of the given significance.
Usage:
FLOOR(Number, Significance)
Example:
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_LISTSorts 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])
Example:
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.
HOURReturns the numeric hour of the day from a datetime expression
Usage:
HOUR( Date )
Example:
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
IFReturns Value 1 if a logical expression is "TRUE", and Value 2 if it is "FALSE”
Usage:
IF( Expression, Value 1, Value 2 )
Example:
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
IFERRORReturns a specified value if the calculation generates an error
Usage:
IFERROR( Expression, Error Value )
Example:
IFERROR( 1 / 0, "Divide by zero!" ) → returns the text "Divide by zero!" IFERROR( [Price] * [Tax], 0 ) → returns 0 if there is an error in the calculation
IFNONEReturns 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_NULLReturns 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_BYReturns a name of a member who last modified a value of a specified field
Usage:
LAST_MODIFIED_BY([Field name])
Example:
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_TIMEReturns a date and a time when a value of the specified field was last changed
Usage:
LAST_MODIFIED_TIME([Field name])
Example:
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
LEFTReturns the first n characters from specified text
Usage:
LEFT( Text, Characters )
Example:
LEFT( "Testing", 4 ) → returns "Test" as text LEFT( ("This is " + "a test"), 9 ) → returns "This is a" as text
LENGTHReturns the numeric character count of specified text
Usage:
LENGTH( Text )
Example:
LENGTH( "Testing" ) → returns 7 LENGTH( "This is " + "a test" ) → returns 14
LIKEReturns TRUE if expression contains specified pattern
Usage:
LIKE( Expression, Pattern )
Example:
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
The following patterns are supported by the LIKE function:
LOGReturns the logarithm of the specified number with the specified base.
Usage:
LOG(Number, Base, Precision)
Example:
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.
LOWERReturns specified text in lower case
Usage:
LOWER( Text )
Example:
LOWER( "Testing" ) → returns "testing" LOWER( "This is " + "a Test" ) → returns "this is a test"
MAXReturns the maximum numeric value from a series of numbers
Usage:
MAX( [Field1], [Field2], ... ) MAX( [Linked Record Field].[Field] )
Example:
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
MEDIANReturns the median ("midpoint") value of a series of numbers
Usage:
MEDIAN( [Number1], [Number2], ... ) MEDIAN( [Linked Record Field].[Number1] )
Example:
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
MIDExtracts 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“
MINReturns the minimum numeric value from a series of numbers
Usage:
MIN( [Number1], [Number2], ... ) MIN( [Linked Record Field].[Number] )
Example:
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
MINUTEReturns the numeric minute from a datetime expression
Usage:
MINUTE( Date )
Example:
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
MODReturns the remainder of a division operation.
Usage:
MOD(Dividend, Divisor)
Example:
MOD(10, 3) → returns 1 MOD(15, 7) → returns 1 MOD(8, 2) → returns 0
MONTHReturns the numeric month from a datetime expression
Usage:
MONTH( Date )
Example:
MONTH( "01-01-2020 01:10:11" ) → returns 1 MONTH( "11-21-2020 02:11:12" ) → returns 11
NOTReturns TRUE if expression is logically false and FALSE otherwise
Usage:
NOT( Expression )
Example:
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
NOWReturns the current date and time as a datetime valueIf you only need the current date, you can use TODAY() instead.
Note that this function is only available during your trial or while on a paid plan.
Usage:
NOW()
Example:
NOW() → returns today’s date and the current time as a datetime value
NUMBERReturns a referenced string as a number.
Usage:
NUMBER( Expression, Precision )
Example:
NUMBER( "12.351" ) → returns 12.35 as a number NUMBER( [Text], 4 ) → if [Text] is "12.35143" returns 12.3514 as a number
NUMERICVALUEReturns the number associated with a single select field or zero if nothing selected. Multi-select fields return the sum of selected values or 0 if none.
Usage:
NUMERICVALUE( [Single or Multi-Select Field] )
Example:
NUMERICVALUE([Score]) = 3 NUMERICVALUE([Categories]) → if [Categories] has two selections with associated numeric values 2 and 3, returns 5
ODDReturns the nearest odd integer greater than or equal to the specified number.
Usage:
ODD( Number )
Example:
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.
ORReturns TRUE if any of the specified expressions are logically true and FALSE otherwise
Usage:
OR( Expression1, Expression2, ... )
Example:
OR( 2 == 1, 1 == 1 ) → returns TRUE OR( [Status] == "Closed", [Status] == "Canceled" ) → returns TRUE if Status is either Closed or Canceled
POWERCalculates the result of raising a number to a specified power. Precision is optional and defaults to 2.
Usage:
POWER(Base, Exponent, Precision)
Example:
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
QUARTERReturns the numeric quarter from a datetime expression
Usage:
QUARTER( Date )
Example:
QUARTER( "01-01-2020 01:10:11" ) → returns 1 QUARTER( "11-21-2020 02:11:12" ) → returns 4
RECORD_IDReturns the unique record id of the current record as text
Usage:
RECORD_ID()
Example:
RECORD_ID() → returns the current record id
RELATED_RECORD_SORTSorts 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])
Example:
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.
RELATED_RECORDS_SORTSorts 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])
Example:
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
REPLACEReplaces specified characters with another set of characters, returning the modified text
Usage:
REPLACE( Text, Text to Replace, Replace with Text )
Example:
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"
REPTReturns a new text string by repeating the specified text string the specified number of times.
Usage:
REPT(Text, Number)
Example:
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.
RFINDReturns 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)
Example:
RFIND(“home“, “Home, sweet home“) → returns 12 RFIND(“e“, “Home, sweet home“, 5, 10) → returns 9 RFIND(“x“, “Home, sweet home“) → returns -1
RIGHTReturns the last n characters from specified text
Usage:
RIGHT( Text, Characters )
Example:
RIGHT( "Testing", 4 ) → returns "ting" as text RIGHT( ("This is " + "a test"), 9 ) → returns "is a test" as text
ROUNDRounds a number to the specified number of decimal places
Usage:
ROUND( Number, Digits )
Example:
ROUND( [Total], 0 ) → if [Total] is 19.95, function returns 20 ROUND( [Total], 1 ) → if [Total] is 12.45, function returns 12.5
ROUNDDOWNReturns the rounded-down value of the specified number to the specified number of digits.
Usage:
ROUNDDOWN(Number, Digits)
Example:
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.
ROUNDUPReturns the rounded-up value of the specified number to the specified number of digits.
Usage:
ROUNDUP(Number, Digits)
Example:
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.
SQRTCalculates the square root of a given number with specified precision.
Usage:
SQRT(Number, Precision)
Example:
SQRT(9) → returns 3.00 SQRT(16, 3) → returns 4.000 SQRT(2, 3) → returns 1.414
STDEVReturns the standard deviation of a series of numbers
Usage:
STDEV( Number1, Number2, ... ) STDEV( [Linked Record].[Field] )
Example:
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
SUMReturns the sum of a series of numbers
Usage:
SUM( Number1, Number2, ... ) SUM( [Linked Record].[Field] )
Example:
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
SUMIFReturns 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
TReturns the argument if it is text and blank otherwise
Usage:
T(Value)
Example:
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.
TEXTReturns the referenced numeric or date expression as text
Usage:
TEXT( Expression )
Example:
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
TODAYReturns the current date as a date value
Note that this function is only available during your trial or while on a paid plan.
Usage:
TODAY()
Example:
TODAY() → returns today’s date as a date value
TOPAccepts 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])
Example:
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.
TRIMRemoves whitespace from the beginning and end of the specified text
Usage:
TRIM( Text )
Example:
TRIM( " Testing " ) → returns "Testing" TRIM( "This is " + "a Test " ) → returns "This is a test"
UPPERReturns specified text in upper case
Usage:
UPPER( Text )
Example:
UPPER( "Testing" ) → returns "TESTING" UPPER( "This is " + "a Test" ) → returns "THIS IS A TEST"
WEEKReturns the numeric week number of the year from a datetime expression
Usage:
WEEK( Date )
Example:
WEEK( "01-01-2020 01:10:11" ) → returns 1 WEEK( "11-21-2020 02:11:12" ) → returns 47
WEEKDAYReturns the day of the week as an integer between 1 and 7. StartDayOfWeek is an optional argument specifying the starting day of the week. It can be either "Monday" or "Sunday". If omitted, weeks start on Monday by default.
Usage:
WEEKDAY(Date, StartDayOfWeek)
Example:
WEEKDAY( TODAY() ) → returns 3, assuming today is Wednesday. WEEKDAY( TODAY(), "Sunday") → returns 4, assuming today is Wednesday.
WORKDAYSReturns the date a specified number of working days (as defined in your SmartSuite profile, including any holidays that have been specified) from the specified start date
Usage:
WORKDAYS( Start Date, Days )
Example:
WORKDAYS( "4/2/2020", 5 ) → returns date 4/9/2020
WORKDAYS_DIFFReturns the number of working days (as defined in your SmartSuite profile, including any holidays that have been specified) between the specified start and end dates (inclusive of the start and end days)
Usage:
WORKDAYS_DIFF( Start Date, End Date )
Example:
WORKDAYS_DIFF( "4/2/2020", "4/7/2020" ) → returns 4 as a number
YEARReturns the numeric year from a datetime expression
Usage:
YEAR( Date )
Example:
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?
DATEADD( Date, Count, Unit ) Unit is one of the following: ∙ minutes ∙ hours ∙ days ∙ weeks ∙ months ∙ years, for example DATEADD([Due Date], 180, "days"). A negative number gives an earlier date.
How do I get the hours between a start time and an end time?
DATEDIFF( Date1, Date2, Unit ) Unit is one of the following: ∙ minutes ∙ hours ∙ days ∙ weeks ∙ months (uses 30 days for calculation) ∙ years with "hours" as the unit: DATEDIFF([Time In], [Time Out], "hours").
How do I count working days between two dates?
WORKDAYS_DIFF( Start Date, End Date ). Working days and holidays come from your SmartSuite profile settings.
How do I remove extra spaces from a text value?
TRIM( Text ) removes spaces at the start and end of the text.
How do I put a value in parentheses?
Join text with CONCAT or the + operator: CONCAT("(", [Field], ")").
How do I total a number across linked records?
SUM([Linked Record].[Number]) adds every linked value; SUMIF( Criteria, [Linked Record].[Number1] ) adds only those that meet a condition.
Last verified 21 Oct 2026 against this article as Product last updated it on 2 Jan 2026; every usage and example unchanged