# 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>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://guides.noloco.io/data/collections/formulas.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.