# Formulas

A formula field contains a value that is calculated from a spreadsheet-like formula. They can perform operations on non-formula fields on the same record, allowing for mathematical, logical, text & date based processing. For example, a *full name* field could be added to the **User** table that concatenates the *first name* and *last name* fields.

### Creating a formula field

* On the data tab of your portal navigate to the table you would like to add the formula field to
* Add a new field and choose **Formula** as the type.
* Enter the formula. To reference another field hit the plus button to add the reference at the current location.

### When do they calculate?

A formula's value for a given record will calculate when any of the following occur:

* When the formula field is created
* When the formula is updated
* When a new record is added
* When a field referenced in a formula is updated

If a formula doesn't reference any other fields it will only be calculated for a given record when:

* A formula is added initially
* The formula is edited
* The record is initially created

### Output Types

When a formula is added or updated, we will check the output and assign a type based on the output patterns. The possible outputs are:

* Boolean (formula must return true or false)
* Date (formula must return value wrapped in `TODATE` operator)
* Decimal
* Integer
* Text

### Examples

Field references will be highlighted green in the below examples

#### Joining text fields

E.g. creating a full name from a first name & last name, ensuring to add a space in between if both fields have a value \&#xNAN;*<mark style="color:green;">**`first name`**</mark>*` `` ``& ` `IF(OR(ISBLANK(`*<mark style="color:green;">**`first name`**</mark>*`),ISBLANK(`*<mark style="color:green;">**`last name`**</mark>*`)),""," ")` ` &`` `` `*<mark style="color:green;">**`last name`**</mark>*

#### Replacing part of a text field

E.g. replace "-old" with " (discontinued)" for a nicer record view

`SUBSTITUTE(`<mark style="color:green;">`name`</mark>`, "-old", " (discontinued)")`

#### Adding days to a date

E.g. creating a deadline a week after an important date

`TODATE(`<mark style="color:green;">`important date`</mark>` `` ``+ 7) `

#### Adding working days to a date

E.g. creating a deadline several working days after an important date, taking holidays and weekends into account

`TODATE(WORKDAY(important date, 3, {"2024-06-03","2024-12-25"})`

#### Calculations

E.g. calculating the gross sales

<mark style="color:green;">`(number sold`</mark>` ``-`` `<mark style="color:green;">`number refunded)`</mark>` ``*`` `<mark style="color:green;">`price`</mark>

### Troubleshooting

Why is my formula not working?

If your formula isn’t working as expected, the most common cause is an **incompatible field type**. For example, trying to perform a calculation on a text field or using a date field in a way that requires a number will cause errors.

A common mistake is when fields are set up as **text fields**, but then used in a formula as if they were **numbers**—for instance, comparing two text values like `"45"` and `"60"` as though they were numeric values. Since they’re stored as text, the formula can’t evaluate the comparison correctly.

**Tip: If you intend to run numeric operations, make sure the fields are set to a number type. Double-checking and adjusting field types usually resolves the issue quickly.**

#### Here's a quick troubleshooting checklist you can follow:

1. **Check field types** — confirm if the fields in your formula are text, number, date, boolean, etc.
2. **Match field type to operation** — ensure the operation you’re performing (e.g., addition, concatenation) is valid for that field type.
3. **Adjust if needed** — update the field type (for example, change from text to number) or rewrite the formula to match the data type.
4. **Test the formula** — Try testing the formula by selecting a record and seeing if the preview result matches what you expect it to be

### Supported Operations

Below is a reference of the officially supported operators in our functions.

#### Dates & Time

<table><thead><tr><th width="150">Operator Name</th><th width="150">Description</th><th>Usage</th></tr></thead><tbody><tr><td>+</td><td>Adds a number of days</td><td><em>date</em> + 1</td></tr><tr><td>-</td><td>Subtracts a number of days</td><td><em>date</em> - 2</td></tr><tr><td>TODATE</td><td>Converts a date to the correct output format for a Noloco date.</td><td>TODATE(<em>date</em>)</td></tr><tr><td>DATE</td><td>Converts a provided day, month and year to a serial date.</td><td>DATE(<em>year</em>, <em>month</em>, <em>day</em>)</td></tr><tr><td>DATEVALUE</td><td>Converts a date from text in one of the two formats:<br>"mm-dd-yyyy" or "yyyy-mm-dd" to a serial date.</td><td>DATEVALUE(<em>dateText</em>)</td></tr><tr><td>DAY</td><td>The day from a date</td><td>DAY(date)</td></tr><tr><td>DAYS</td><td>Number of days between two dates</td><td>DAYS(<em>end</em>, <em>start</em>)</td></tr><tr><td>WORKDAY</td><td>Date representing the number of working days before or after the starting date. Working days exclude weekends and any dates identified as holidays.</td><td>WORKDAY(start, numberOfDays, [holidays]</td></tr><tr><td>EDATE</td><td>Date representing a date a number of months before or after provided date</td><td>EDATE(<em>date, numberOfMonths</em>)</td></tr><tr><td>EOMONTH(<em>date, numberOfMonths</em>)</td><td>Date representing the last day of a month a provided number of months before or after provided date</td><td>EOMONTH(<em>date, numberOfMonths</em>)</td></tr><tr><td>HOUR</td><td>The hours from a datetime or time value</td><td>HOUR(<em>datetime</em>)</td></tr><tr><td>ISOWEEKNUM</td><td>The ISO week number a date falls on</td><td>ISOWEEKNUM(<em>date</em>)</td></tr><tr><td>MINUTE</td><td>The minutes from a valid datetime or time value.</td><td>MINUTE(<em>datetime</em>)</td></tr><tr><td>MONTH</td><td>The month of a provided date in numeric format</td><td>MONTH(<em>date</em>)</td></tr><tr><td>SECOND</td><td>The seconds from a valid datetime or time value.</td><td>SECOND(<em>datetime</em>)</td></tr><tr><td>TIME</td><td>Converts a provided hour, minute and second to a fractional time.</td><td>TIME(<em>hour, minute,second</em>)</td></tr><tr><td>TIMEVALUE</td><td><p>Converts a time in the accepted formats to a valid fractional time.<br>Accepts:<br><code>"1:10 AM"</code></p><p><code>"1:10:05 AM"</code></p><p><code>"18:20"</code><br><code>"18:20:15"</code></p></td><td>TIMEVALUE(<em>timeText</em>)</td></tr><tr><td>WEEKNUM</td><td>The week number the date falls on</td><td>WEEKNUM(<em>date</em>)</td></tr><tr><td>YEAR</td><td>The year from a date</td><td>YEAR(<em>date</em>)</td></tr><tr><td>YEARFRAC</td><td>The number of years and fractional years between two dates</td><td>YEARFRAC(<em>start,end</em>)</td></tr></tbody></table>

#### Logic

| Operator | Action                                                              | Example Usage                                               |
| -------- | ------------------------------------------------------------------- | ----------------------------------------------------------- |
| <        | First is less than second                                           | `1 < 2`                                                     |
| >        | First is greater than second                                        | `1 > 2`                                                     |
| =        | First equals second                                                 | `1 = 2`                                                     |
| AND      | Returns true if all arguments are true                              | `AND(logicValue,logicValue,...)`                            |
| FALSE    | Returns the value of false                                          | FALSE()                                                     |
| IF       | Returns different value based on whether the first argument is true | `IF(logicValue, trueValue, falseValue)`                     |
| NOT      | Returns the opposite of a logical value                             | <p><code>NOT(TRUE())</code><br><em>returns FALSE()</em></p> |
| OR       | Returns true if any argument is true                                | `OR(logicValue,logicValue,...)`                             |
| TRUE     | Returns the value of true                                           | TRUE()                                                      |
| NULL     | Returns the value of `null`                                         | NULL()                                                      |
| ISNUMBER | Returns true if the argument is a number                            | ISNUMBER(value)                                             |

#### Math

<table><thead><tr><th width="150">Operator</th><th width="150">Action</th><th>Usage Example</th></tr></thead><tbody><tr><td>+</td><td>Adds the values to either side</td><td><em>10 + 2</em></td></tr><tr><td>-</td><td>Subtracts the values to either side</td><td><em>10 - 2</em></td></tr><tr><td>/</td><td>Divides the values to either side</td><td>10 / 2</td></tr><tr><td>*</td><td>Multiplies the values to either side</td><td>10 * 2</td></tr><tr><td>ABS</td><td>Absolute value</td><td>ABS(number)</td></tr><tr><td>CEILING</td><td>Rounds a number up to the closest multiple of significance</td><td>CEILING(number, significance)</td></tr><tr><td>DECIMAL</td><td>Converts text of a number to a decimal</td><td>DECIMAL(text)</td></tr><tr><td>EVEN</td><td>Rounds a number to nearest even integer</td><td>EVEN(number)</td></tr><tr><td>FACT</td><td>Factorial of a number</td><td>FACT(number)</td></tr><tr><td>FLOOR</td><td>Rounds a number down to the closest multiple of significance</td><td>FLOOR(number,significance)</td></tr><tr><td>INT</td><td>Rounds a number to the nearest integer (less than or equal)</td><td>INT(number)</td></tr><tr><td>LOG</td><td>Logarithm of a number for a provided base</td><td>LOG(number, base)</td></tr><tr><td>MOD</td><td>Modulo of a number</td><td>MOD(number, divisor)</td></tr><tr><td>ODD</td><td>Rounds a number to nearest odd integer</td><td>ODD(number)</td></tr><tr><td>POWER</td><td>Raises a number to a power</td><td>POWER(number, power)</td></tr><tr><td>PRODUCT</td><td>Multiplies the arguments</td><td>PRODUCT(number, number2,...)</td></tr><tr><td>ROUND</td><td>Rounds a number to the closest value for a number of decimal places</td><td>ROUND(number, places)</td></tr><tr><td>ROUNDDOWN</td><td>Rounds a number down for a number of decimal places</td><td>ROUNDDOWN()ROUND(number, places)</td></tr><tr><td>ROUNDUP</td><td>Rounds a number up for a number of decimal places</td><td>ROUNDUP(number, places)</td></tr><tr><td>SIGN</td><td><ul><li><code>-1</code> if number is negative</li><li><code>0</code> if zero</li><li><code>1</code> if positive</li></ul></td><td>SIGN(number)</td></tr><tr><td>SQRT</td><td>Positive square root of a positive number</td><td>SQRT(number)</td></tr><tr><td>SUM</td><td>Sums the arguments</td><td>SUM(number, number2,...)</td></tr><tr><td>TRUNC</td><td>Returns the integer component of a number</td><td>TRUNC(number)</td></tr></tbody></table>

#### Text

<table><thead><tr><th width="150">Operator</th><th width="150">Action</th><th>Usage Example</th></tr></thead><tbody><tr><td>&#x26;</td><td>Appends two strings to one another</td><td><em><code>"Hello " &#x26; "World"</code></em></td></tr><tr><td>CLEAN</td><td>Removes non printable characters from a string</td><td>CLEAN(text)</td></tr><tr><td>CONCATENATE</td><td>Appends two strings to one another</td><td><code>CONCATENATE(text1, text2)</code></td></tr><tr><td>EXACT</td><td>Checks if two strings are identical</td><td>EXACT(text1,text2)</td></tr><tr><td>FIND</td><td>Returns the first position a string occurs in text</td><td>FIND(searchQuery, text)</td></tr><tr><td>ISBLANK</td><td>Returns true if a string is blank</td><td>ISBLANK(text)</td></tr><tr><td>LEFT</td><td>Returns a substring from the beginning of a string</td><td>LEFT(text, numberOfCharacters)</td></tr><tr><td>LEN</td><td>Returns the length of a string</td><td></td></tr><tr><td>LOWER</td><td>Returns a string in lowercase</td><td>LOWER(text)</td></tr><tr><td>UPPER</td><td>Returns a string in uppercase</td><td>UPPER(text)</td></tr><tr><td>MID</td><td>Returns a substring from the middle of a string</td><td>MID(text, startPosition, numberOfCharacters)</td></tr><tr><td>PROPER</td><td>Returns a string with words capitalized</td><td>PROPER(text)</td></tr><tr><td>REPLACE</td><td>Replaces part of a string with another provided string</td><td>REPLACE(text,startPosition, replacementLength, replacementText)</td></tr><tr><td>REPT</td><td>Repeats a string a number of times</td><td>REPT(text, numberOfTimes)</td></tr><tr><td>RIGHT</td><td>Returns a substring from the end of a string</td><td>RIGHT(text, numberOfCharacters)</td></tr><tr><td>SEARCH</td><td>Returns the first position in a string occurs in text after the start character position</td><td>SEARCH(searchQuery, text, startPosition)</td></tr><tr><td>SUBSTITUTE</td><td>Replaces all occurrences of a provided query with the provided replacement in a string</td><td>SUBSTITUTE(text, searchQuery, replacement)</td></tr><tr><td>TEXT</td><td>Converts a number into text according to a specified format</td><td>TEXT(number, format)</td></tr><tr><td>TRIM</td><td>Removes spaces from the beginning &#x26; end</td><td>TRIM(text)</td></tr><tr><td>UPPER</td><td>Returns a string in uppercase</td><td>UPPER(text)</td></tr></tbody></table>