Appearance
Formula and Validation Rules reference
This reference lists the operators, functions, signatures, and examples supported by Validation Rules, Formula fields, and default-value formulas.
Overview
Use this reference to write Formula fields, default values, and Validation Rules. It covers supported Field Type values, operators, function parameters, and common expressions.
Supported field types
The formula engine supports:
- Amount, percentage, decimal, and number.
- Date, Date Time, and time.
- Single-Line Text, Multi-Line Text, email, URL, address, and mobile number.
- Boolean.
- Location, Check-In, and payment components.
- Formula and Roll-Up Summary.
- Formula and Roll-Up Summary fields reached through a cross-object Lookup.
Operators and functions
| Return type | Operator or function | Parameters | Description | Example |
|---|---|---|---|---|
| General | () | None | Changes evaluation precedence. | (3 + 2) * 5 |
| General | IF(logical_test, value_if_true, value_if_false) | Three; results must share a type | Returns the second value when true, otherwise the third. | IF(true, 34, 52) returns 34 |
| General | CASE(expression, value1, result1, ..., else_result) | Variable; all results share a type | Compares in order and returns the matching result or fallback. | CASE(3, 2, 2, 3, 33, 1.3) returns 33 |
| General | NULLVALUE(expression, substitute_expression) | Two | Returns the substitute when the expression is null. | NULLVALUE(Null, 1) returns 1 |
| Number | +, -, *, / | Two numeric values | Performs arithmetic. | (3 + 2) * 6 / 5 returns 6 |
| Number | Date - date | Two dates | Returns the difference in days. | End Date minus Start Date |
| Number | Date-time - date-time | Two date-times | Returns the difference in hours. | Two timestamps |
| Number | Time - time | Two times | Returns the difference in hours. | 17:00:00 - 15:00:00 returns 2 |
| Number | VALUE(string) | Text | Converts text to a number or returns Null. | VALUE('-1982.0413') |
| Number | MIN(number1, number2) | Two numbers | Returns the smaller number. | MIN(4, 13) returns 4 |
| Number | MAX(number1, number2) | Two numbers | Returns the larger number. | MAX(4, 13) returns 13 |
| Number | MULTIPLE(number1, number2) | Two numbers | Multiplies two numbers. | MULTIPLE(4, 13) returns 52 |
| Number | MOD(number1, number2) | Two numbers | Returns the remainder. | MOD(13, 4) returns 1 |
| Number | ADDS(number1, number2) | Two numbers | Adds two numbers. | ADDS(13, 4) returns 17 |
| Number | SUBTRACTS(number1, number2) | Two numbers | Subtracts the second number. | SUBTRACTS(13, 4) returns 9 |
| Number | YEAR(date) | Date or Date Time | Returns the year. | YEAR(DATEVALUE('1982-04-13')) |
| Number | MONTH(date) | Date or Date Time | Returns the month. | MONTH(DATEVALUE('1982-04-13')) |
| Number | DAY(date) | Date or Date Time | Returns the day of month. | DAY(DATEVALUE('1982-04-13')) |
| Number | LEN(text) | Text | Returns character count. | LEN('xiaoke') returns 6 |
| Duration | YEARS(number) | Number | Creates a year offset. | TODAY() + YEARS(1) |
| Duration | MONTHS(number) | Number | Creates a month offset. | TODAY() + MONTHS(2) |
| Duration | DAYS(number) | Number | Creates a day offset. | TODAY() - DAYS(7) |
| Duration | HOURS(number) | Number | Creates an hour offset. | NOW() + HOURS(4) |
| Duration | MINUTES(number) | Number | Creates a minute offset. | NOW() - MINUTES(30) |
| Date | +, - | Date and offset | Adds or subtracts an offset. | Created On plus four days |
| Date | DATE(year, month, day) | Three numbers | Constructs a date. | DATE(1982, 4, 13) |
| Date | DATEVALUE(string) | Text | Parses a date. | DATEVALUE('1982-04-13') |
| Date | TODAY() | None | Returns the current date. | 2026-07-02 |
| Date | DATETIMETODATE(datetime) | Date Time | Extracts the date. | DATETIMETODATE(NOW()) |
| Date Time | +, - | Date Time and offset | Adds or subtracts an offset. | Deadline minus one day |
| Date Time | DATETIMEVALUE(string) | Text | Parses a Date Time. | DATETIMEVALUE('2001-08-24 15:45:25') |
| Date Time | NOW() | None | Returns the current Date Time. | 2026-07-02 17:38:00 |
| Time | DATETIMETOTIME(datetime) | Date Time | Extracts the time. | DATETIMETOTIME(NOW()) |
| Text | & | Two text values | Concatenates text. | 'A' & 'B' returns AB |
| Text | '' | One quoted value | Declares Single-Line Text. | 'text' |
| Text | '''''' | One triple-quoted value | Declares Multi-Line Text. | '''multiple lines''' |
| Text | NUMBERSTRING(number) | Number or amount | Converts a number to uppercase Chinese numerals. | NUMBERSTRING(198204.13) |
| Text | NUMBERSTRINGRMB(number) | Number or amount | Converts a number to uppercase RMB text. | NUMBERSTRINGRMB(198204.13) |
| Boolean | <, >, >=, <=, != | Two comparable values | Compares values. | 1 < 2 returns true |
| Boolean | AND(boolean1, ...) | Boolean values | Returns true when all values are true. | AND(2 > 1, 5 > 3) |
| Boolean | OR(boolean1, ...) | Boolean values | Returns true when any value is true. | OR(2 > 1, 5 < 3) |
| Boolean | NOT(boolean) | Boolean | Negates the value. | NOT(2 > 1) |
| Boolean | ISNULL(expression) | Any value | Tests whether a value is null. | ISNULL(Mobile) |
| Boolean | ISNUMBER(string) | Text | Tests whether text can become a number. | ISNUMBER('5') |
| Boolean | STARTWITH(string1, string2) | Two text values | Tests the start of text. | STARTWITH('abcdef', 'ab') |
| Boolean | ENDWITH(string1, string2) | Two text values | Tests the end of text. | ENDWITH('aecdab', 'ab') |
| Boolean | EQUALS(string1, string2) | Two text values | Performs a case-sensitive comparison. | EQUALS('Abc', 'abc') |
| Boolean | CONTAINS(string1, string2) | Two text values | Tests whether text contains a value. | CONTAINS('abcdef', 'cd') |
Common Validation Rules
1. End Date cannot precede Start Date
text
结束日期 < 开始日期2. Completion Date is required when status is complete
text
AND(EQUALS(状态, '已完成'), ISNULL(完成日期))3. Discount must not exceed 30%, and amount must exceed zero
text
OR(折扣率 > 0.3, 金额 <= 0)4. Mobile or telephone is required
text
AND(ISNULL(手机), ISNULL(电话))