Formulas

Create and manage formulas used in the data grid.

​

Create Formula

Open the Formula Editor to create a formula.

​

Edit Formula

Update an existing formula.

​

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 Name	Syntax and 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 Name	Syntax and Description
AND	AND(logical1,logical2,…) 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.
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 Name	Syntax and 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 Name	Syntax and 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.