Formulas

Create formulas for grid columns and other inputs that support expressions, including actions.

Create Formula

Open the Formula Editor to create a formula.

Open Formula Editor

Open the Formula Editor from one of the following.

From the Columns dialog, open the Formula tab and click Formula Editor
From inputs that support formulas, click the Formula Editor

Enter Formula Details

Enter the required information.

Field	Description
Column Label	The header text shown in the data grid
Data Type	The formula’s output type
Formula	Expression using fields, operators, and functions
Treat blank fields as zeroes / blanks	How blanks are evaluated

Validate

Click Validate to check the formula syntax.

Apply

Click Apply to use the formula.

Edit Formula

Update an existing formula.

Open Formula Editor

Open the Formula Editor from one of the following.

From a formula column, open the Edit Column dialog and click Formula Editor.
From inputs that support formulas, click the Formula Editor

Update Formula

Modify the formula and settings.

Save

Click Save to apply changes.

Formula Syntax

Understand how to write formulas.

Type formulas directly or insert from the editor pickers
Insert fields, functions, and operators at the end of the current formula
Use parentheses () to control calculation order
Field names are case-sensitive; functions and operators are not

Built-in Functions

Date & Time

Function	Syntax	Description
`ADDMONTHS`	`ADDMONTHS(date,num)`	Add the num months to the date, using the last date of the month if date is the last day of the month or adding num months has fewer days.
`DATE`	`DATE(year,month,day)`	Creates a date from a year, month and day.
`DATETIMEVALUE`	`DATETIMEVALUE(expression)`	Returns a year, month, day and GMT time value.
`DATEVALUE`	`DATEVALUE(expression)`	Creates a date from its datetime or text representation.
`DAY`	`DAY(date)`	Returns the day of the month, a number between 1 and 31.
`DAYOFYEAR`	`DAYOFYEAR(date)`	Return the day of the calendar year (from 1-366).
`FORMATDURATION`	`FORMATDURATION(numSeconds[, includeDays] \| dateTime/time, dateTime/time)`	Format the number of seconds with optional days, or the difference between times or dateTimes as HH:MI:SS.
`FROMUNIXTIME`	`FROMUNIXTIME(number)`	Return the datetime that represents the given number as the seconds elapsed since 1 Jan 1970.
`HOUR`	`HOUR(expression)`	Returns hour of day.
`ISOWEEK`	`ISOWEEK(date)`	Return the ISO 8601 week number for the given date (from 1-53) so that the first week starts on monday.
`ISOYEAR`	`ISOYEAR(date)`	Return the ISO 8601 week-numbering 4-digit year for the given date so that the first day is a monday.
`MILLISECOND`	`MILLISECOND(expression)`	Returns millisecond of day.
`MINUTE`	`MINUTE(expression)`	Returns minute of day.
`MONTH`	`MONTH(date)`	Returns the month, a number between 1 (January) and 12 (December).
`NOW`	`NOW()`	Returns a datetime representing the current moment.
`SECOND`	`SECOND(expression)`	Returns second of day.
`TIMENOW`	`TIMENOW()`	Returns a time representing the current moment.
`TIMEVALUE`	`TIMEVALUE(expression)`	Returns a time.
`TODAY`	`TODAY()`	Returns the current date.
`UNIXTIMESTAMP`	`UNIXTIMESTAMP(date/time)`	Return the number of seconds since 1 Jan 1970 for the given date, or number of seconds in the day for a time.
`WEEKDAY`	`WEEKDAY(date)`	Return the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday.
`YEAR`	`YEAR(date)`	Returns the year of a date, a number between 1900 and 9999.

Logical

Function	Syntax	Description
`AND`	`AND(logical1,logical2,...)`	Returns TRUE if all arguments are true; otherwise returns FALSE.
`BLANKVALUE`	`BLANKVALUE(expression, substitute_expression)`	Checks whether expression is blank and returns substitute_expression if it is blank. If expression is not blank, returns the original expression value.
`CASE`	`CASE(expression, value1, result1, value2, result2,...,else_result)`	Checks an expression against a series of values. If the expression compares equal to any value, the corresponding result is returned. If it is not equal to any of the values, the else-result is returned.
`IF`	`IF (logical_test, value_if_true, value_if_false)`	Checks whether a condition is true, and returns one value if TRUE and another value if FALSE.
`ISBLANK`	`ISBLANK(expression)`	Checks whether an expression is blank and returns TRUE or FALSE.
`ISNULL`	`ISNULL(expression)`	Checks whether an expression is null and returns TRUE or FALSE.
`ISNUMBER`	`ISNUMBER(Text)`	Returns TRUE if the text value is a number. Otherwise, it returns FALSE.
`NOT`	`NOT(logical)`	Changes FALSE to TRUE or TRUE to FALSE.
`NULLVALUE`	`NULLVALUE (expression, substitute_expression)`	Checks whether expression is null and returns substitute_expression if it is null. If expression is not null, returns the original expression value.
`OR`	`OR(logical1,logical2,...)`	Checks whether any of the arguments are true and returns TRUE or FALSE. Returns FALSE only if all arguments are false.

Math

Function	Syntax	Description
`ABS`	`ABS(number)`	Returns the absolute value of a number, a number without its sign.
`ACOS`	`ACOS(number)`	Returns the arc cosine of the number in radians, if the given number is between -1 and 1. Otherwise NULL.
`ASIN`	`ASIN(number)`	Returns the arc sine of the number in radians, if the given number is between -1 and 1. Otherwise NULL.
`ATAN`	`ATAN(number)`	Returns the arc tangent of the number in radians.
`ATAN2`	`ATAN2(y, x)`	Returns the arc tangent of the quotient of y and x in radians.
`CEILING`	`CEILING(number)`	Rounds a number up to the nearest integer, away from zero if negative.
`COS`	`COS(number)`	Returns the cosine of the number, where the number given in radians.
`EXP`	`EXP(number)`	Returns e raised to the power of a given number.
`FLOOR`	`FLOOR(number)`	Rounds a number down, towards zero to the nearest integer.
`LN`	`LN(number)`	Returns the natural logarithm of a number.
`LOG`	`LOG(number)`	Returns the base 10 logarithm of n.
`MAX`	`MAX(number,number,...)`	Returns the greatest of all the arguments.
`MCEILING`	`MCEILING(number)`	Rounds a number up to the nearest integer, towards zero if negative.
`MFLOOR`	`MFLOOR(number)`	Rounds a number down to the nearest integer, away from zero if negative.
`MIN`	`MIN(number,number,...)`	Returns the least of all the arguments.
`MOD`	`MOD(number,divisor)`	Returns the remainder after a number is divided by a divisor.
`PI`	`PI()`	Returns pi.
`ROUND`	`ROUND(number,num_digits)`	Rounds a number to a specified number of digits.
`SIN`	`SIN(number)`	Returns the sine of the number, where the number given in radians.
`SQRT`	`SQRT(number)`	Returns the positive square root of a number.
`TAN`	`TAN(number)`	Returns the tangent of the number, where the number given in radians.
`TRUNC`	`TRUNC(number,num_digits)`	Truncates a number to a specified number of digits.

Text

Function	Syntax	Description
`ASCII`	`ASCII(text)`	Return the first character’s code point from the given string as a number.
`BEGINS`	`BEGINS(text, compare_text)`	Checks if text begins with specified characters and returns TRUE if it does. Otherwise returns FALSE.
`BR`	`BR()`	Inserts an HTML break tag in string formulas.
`CASESAFEID`	`CASESAFEID(id)`	Converts a 15-character ID into a case insensitive 18-character ID.
`CHR`	`CHR(number)`	Return a string with the first character’s code point as the given number.
`CONTAINS`	`CONTAINS(text, compare_text)`	Checks if text contains specified characters, and returns TRUE if it does. Otherwise, returns FALSE.
`FIND`	`FIND(search_text, text [, start_num])`	Returns the position of the search_text string in text.
`HYPERLINK`	`HYPERLINK(url, friendly_name [, target])`	Creates a hyperlink.
`IMAGE`	`IMAGE(image_url, alternate_text [, height, width])`	Inserts an image.
`INCLUDES`	`INCLUDES(multiselect_picklist_field, text_literal)`	Determines if any value selected in a multi-select picklist field equals a text literal you specify.
`INITCAP`	`INITCAP(text)`	Return the text as lowercase with first character of each word made uppercase.
`ISPICKVAL`	`ISPICKVAL(picklist_field, text_literal)`	Checks whether the value of a picklist field is equal to a string literal.
`LEFT`	`LEFT(text, num_chars)`	Returns the specified number of characters from the start of a text string.
`LEN`	`LEN(text)`	Returns the number of characters in a text string.
`LOWER`	`LOWER(text)`	Converts all letters in the value to lowercase.
`LPAD`	`LPAD(text, padded_length [, pad_string])`	Pad the left side of the value with spaces or the optional pad string so that the length is padded_length.
`MID`	`MID(text, start_num, num_chars)`	Returns character from the middle of a text string, given a starting position and length.
`PICKLISTCOUNT`	`PICKLISTCOUNT(multiselect_picklist_field)`	Returns the number of selected values in a multi-select picklist.
`REVERSE`	`REVERSE(text)`	Returns the text string in reverse order.
`RIGHT`	`RIGHT(text, num_chars)`	Returns the specified number of characters from the end of a text string.
`RPAD`	`RPAD(text, padded_length [, pad_string])`	Pad the right side of the value with spaces or the optional pad string so that the length is padded_length.
`SUBSTITUTE`	`SUBSTITUTE(text, old_text, new_text)`	Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string.
`TEXT`	`TEXT(value)`	Converts a value to text using standard display format.
`TRIM`	`TRIM(text)`	Removes all spaces from a text string except for single spaces between words.
`UPPER`	`UPPER(text)`	Converts all letters in the value to uppercase.
`VALUE`	`VALUE(text)`	Converts a text string that represents a number to a number.

Visualization

Grid

Related Grid

Related Detail

Create Formula

Edit Formula

Formula Syntax

Built-in Functions

Date & Time

Logical

Math

Text

​Create Formula

​Edit Formula

​Formula Syntax

​Built-in Functions

​Date & Time

​Logical

​Math

​Text

Create Formula

Edit Formula

Formula Syntax

Built-in Functions

Date & Time

Logical

Math

Text