The Formula field type allows you to create calculated fields using predefined formulas and variables.
🔢Math
| Function | Description | Syntax | Example |
|---|
ABS | Absolute value | ABS(num1) | ABS(-1) = 1 |
DIVIDE | Division | DIVIDE(num1, num2) | DIVIDE(10, 2) = 5 |
MAX | Maximum value | MAX(num1, num2, ...) | MAX(1, 5, 3) = 5 |
MIN | Minimum value | MIN(num1, num2, ...) | MIN(1, 5, 3) = 1 |
MOD | Modulo (remainder) | MOD(num1, num2) | MOD(10, 3) = 1 |
MULTIPLY | Multiplication | MULTIPLY(num1, num2) | MULTIPLY(3, 4) = 12 |
ROUND | Round to nearest integer | ROUND(num, digits) | ROUND(3.7) = 4 |
ROUNDDOWN | Round down | ROUNDDOWN(num, digits) | ROUNDDOWN(3.7) = 3 |
ROUNDUP | Round up | ROUNDUP(num, digits) | ROUNDUP(3.2) = 4 |
SUBTRACT | Subtraction | SUBTRACT(num1, num2) | SUBTRACT(10, 3) = 7 |
SUM | Sum of values | SUM(num1, num2, ...) | SUM(1, 2, 3) = 6 |
📝Text
| Function | Description | Syntax | Example |
|---|
CONCAT | Concatenate text | CONCAT(text1, text2, ...) | CONCAT("Hello", " ", "World") = "Hello World" |
LEFT | Extract leftmost characters | LEFT(text, num) | LEFT("Hello", 2) = "He" |
LEN | Length of text | LEN(text) | LEN("Hello") = 5 |
LOWER | Convert to lowercase | LOWER(text) | LOWER("HELLO") = "hello" |
MID | Extract middle characters | MID(text, start, num) | MID("Hello", 2, 3) = "ell" |
PROPER | Convert to proper case | PROPER(text) | PROPER("hello world") = "Hello World" |
REPLACE | Replace text | REPLACE(text, start, num, new) | REPLACE("Hello", 2, 2, "a") = "Halo" |
RIGHT | Extract rightmost characters | RIGHT(text, num) | RIGHT("Hello", 2) = "lo" |
TRIM | Remove extra spaces | TRIM(text) | TRIM(" Hello ") = "Hello" |
UPPER | Convert to uppercase | UPPER(text) | UPPER("hello") = "HELLO" |
📅Date & Time
| Function | Description | Syntax | Example |
|---|
DATE | Create a date | DATE(year, month, day) | DATE(2024, 1, 15) = 1/15/2024 |
DATEDIF | Difference between dates | DATEDIF(date1, date2, unit) | DATEDIF(DATE(2024,1,1), DATE(2024,12,31), "D") = 365 |
DAY | Extract day | DAY(date) | DAY(DATE(2024, 1, 15)) = 15 |
EDATE | Add months to date | EDATE(date, months) | EDATE(DATE(2024, 1, 15), 3) = 4/15/2024 |
EOMONTH | End of month | EOMONTH(date, months) | EOMONTH(DATE(2024, 1, 15), 0) = 1/31/2024 |
HOUR | Extract hour | HOUR(time) | HOUR(TIME(14, 30, 0)) = 14 |
MINUTE | Extract minute | MINUTE(time) | MINUTE(TIME(14, 30, 0)) = 30 |
MONTH | Extract month | MONTH(date) | MONTH(DATE(2024, 1, 15)) = 1 |
NOW | Current date and time | NOW() | NOW() = 1/15/2024 2:30 PM |
TIME | Create a time | TIME(hour, minute, second) | TIME(14, 30, 0) = 2:30 PM |
TODAY | Current date | TODAY() | TODAY() = 1/15/2024 |
WEEKDAY | Day of week | WEEKDAY(date) | WEEKDAY(DATE(2024, 1, 15)) = 2 |
WEEKNUM | Week number | WEEKNUM(date) | WEEKNUM(DATE(2024, 1, 15)) = 3 |
YEAR | Extract year | YEAR(date) | YEAR(DATE(2024, 1, 15)) = 2024 |
⚙️Logic
| Function | Description | Syntax | Example |
|---|
AND | Logical AND | AND(condition1, condition2, ...) | AND(TRUE, TRUE) = TRUE |
IF | Conditional statement | IF(condition, value_if_true, value_if_false) | IF(5 > 3, "Yes", "No") = "Yes" |
IFS | Multiple conditions | IFS(condition1, value1, condition2, value2, ...) | IFS(5 > 10, "A", 5 > 3, "B") = "B" |
NOT | Logical NOT | NOT(condition) | NOT(TRUE) = FALSE |
OR | Logical OR | OR(condition1, condition2, ...) | OR(TRUE, FALSE) = TRUE |
SWITCH | Switch between values | SWITCH(value, case1, result1, case2, result2, ...) | SWITCH(2, 1, "A", 2, "B") = "B" |
XOR | Exclusive OR | XOR(condition1, condition2) | XOR(TRUE, FALSE) = TRUE |
🔍Comparison
| Function | Description | Syntax | Example |
|---|
EQ | Equal to | EQ(value1, value2) | EQ(5, 5) = TRUE |
GT | Greater than | GT(value1, value2) | GT(5, 3) = TRUE |
GTE | Greater than or equal to | GTE(value1, value2) | GTE(5, 5) = TRUE |
LT | Less than | LT(value1, value2) | LT(3, 5) = TRUE |
LTE | Less than or equal to | LTE(value1, value2) | LTE(5, 5) = TRUE |
📊Stats
| Function | Description | Syntax | Example |
|---|
AVERAGE | Average value | AVERAGE(num1, num2, ...) | AVERAGE(1, 2, 3) = 2 |
COUNT | Count non-empty values | COUNT(value1, value2, ...) | COUNT(1, 2, "", 4) = 3 |
MEDIAN | Median value | MEDIAN(num1, num2, ...) | MEDIAN(1, 2, 3) = 2 |
COUNTEMPTY | Count empty values | COUNTEMPTY(value1, value2, ...) | COUNTEMPTY(1, "", 3, "") = 2 |
Variables return the value of a field for each entry. You can reference any field in your formula using variables. Both variables and formulas can be added automatically via the available buttons in the Write Formula section.
Here are some practical examples of formulas using available functions and variables:
| Formula Name | Formula | Description |
|---|
| Calculate total price | MULTIPLY({Price}, {Quantity}) | Multiplies the Price field by the Quantity field |
| Format full name | CONCAT({First Name}, " ", {Last Name}) | Combines first and last name fields with a space |
| Calculate age | DATEDIF({Date of Birth}, TODAY(), "Y") | Calculates years between birth date and today |
| Conditional discount | IF(GT({Order Total}, 100), MULTIPLY({Order Total}, 0.9), {Order Total}) | Applies 10% discount if order total exceeds 100 |
| Extract area code | LEFT({Phone Number}, 3) | Extracts the first 3 characters from a phone number field |
| Calculate average rating | AVERAGE({Rating 1}, {Rating 2}, {Rating 3}) | Finds the average of multiple rating fields |
| Status based on date | IF(LT({Due Date}, TODAY()), "Overdue", "On Track") | Marks items as overdue if due date has passed |
| Combine and format text | UPPER(CONCAT({Department}, " - ", {Project Name})) | Creates an uppercase combined field |
- When creating a new field, choose Formula as the data type
- The Write Formula section will appear
- Write your formula using available functions and variables
- Both variables and formulas can be added automatically via the available buttons
After creating your formula field, you can add it to the entry layout so it appears in the system. For more information, refer to Creating Fields.
