# Notion Formula Fundamentals

Please enjoy this free segment from the course Notion A-to-Z. For all lessons in an intuitive sequence, plus videos, practical exercises, functional demos and certification questions, consider enrolling in this novel educational experience.

For each item in a database, a `Formula` property references other properties to make calculations, modify values or customize formatting. To prescribe these operations, you compose a "formula" comprising inputs and actions.

For example, a `Formula` property in a People database can calculate each person's age by referencing a Birthday property. A formula can also reference a Full Name property to automate the First Name and Last Name properties.

Formulas are like mini computer programs, which you'll learn to write in this introduction and subsequent lessons. If you're familiar with functions in Excel or Google Sheets, you'll catch on especially quickly. Any experience with JavaScript gives you an even greater head start.

Like software developers, few Notion users ever attain a comprehensive understanding of formulas. You'll learn the fundamentals, then apply them creatively over time to expand your expertise incrementally.

## The Utility of Formulas

#### Calculate

By referencing one or more other properties, a formula can calculate new values. To calculate Age, for example, a formula can subtract the Birthday property from the current time:

#### Modify

A formula can produce a modified version of one or more other properties, such as extracting a segment or merging multiple values. From a Full Name property, formulas can automatically extract First Name and Last Name:

#### Format

Notion's native formatting choices are quite limited. With formulas, you can redisplay values in custom formats. They're particularly useful for dates, which you can reformat in virtually any fashion:

#### Workaround

Formulas also provide creative methods for sidestepping some of Notion's unintended obstacles. One common example is "rolling up" a `Rollup`.

##### "Roll up" a `Rollup`

When configuring a `Rollup` property, you select a property from a related database to retrieve, or "roll up." Notion prohibits you from selecting a `Rollup` property from that related database. However, you can retrieve a `Formula` property. Therefore, to "roll up" a `Rollup`, you can create a `Formula` property in the related database that simply reflects the value of the desired `Rollup`.

In the States database below, we want to retrieve the People Count property from Organizations. Because People Count is a `Rollup`, we're unable to choose it. Therefore, the "People Count (Reflection)" property, a `Formula` property, simply reflects the value, which we can select in our `Rollup`.

## The Formula Window

To create a formula, you add a property and choose the `Formula` type. You can then click the name of the property and choose `Edit formula`, which opens the Formula Window.

You compose the formula at the top of the Formula Window. Below that composition area, you'll find "ingredients" that can be quickly added to your formula, which you'll learn about in this and subsequent lessons.

Clicking `Done` saves your formula. It executes and returns a value for each item in the database.

## Data Types

Working with formulas, and databases at large, requires a sound understanding of data types. Every value within a database property is one of four data types:

• `Number`
• `String` (Text)
• `Boolean` (`true` or `false`)
• `Date`

Regardless of formatting, `Number` properties display `numbers`; `Date` properties display `dates`; `Checkboxes` display `booleans`; and most other properties are textual `strings`, including `Select` and `Person` properties.

Sometimes, a value can appear to be one type when in fact it's another. The value of a `Text` property is always a `string`, even if you type `1234` or `01/01/2024`. Pay attention to alignment: `numbers` always align to the right, while `strings` align left:

Data types are important because your formula inputs need to meet specified criteria, which typically includes matching data types. Among the most frequent errors you'll encounter is a `type mismatch`, which you'll see at the bottom of your Formula Window:

Notion offers methods of converting a value's data type, which we'll explore below and in subsequent lessons.

## Ingredients of a Formula

For each item in the database, a `Formula` property produces a returned value. To do so, it applies your prescribed actions to your provided input values. Some formulas return `numbers`, others return `strings`, `booleans` or `dates`.

A formula can be as simple as a single input value (`2`) or a complex combination of actions and inputs nested within one another. Ultimately, however, a formula always returns a single value.

### Input Values

You supply the values on which your formula operates in a few ways: literal values, constants and property references. A formula can be as simple as a standalone input value, such as the number `2`, which returns that value for each item in the database:

#### Literal Values

A literal value is information you type directly into the formula, rather than a dynamic reference to another property. As the formula executes for each item in the database, it uses the same literal values.

The `2` in the above example is a literal value of the `number` type, as indicated by its right alignment. To enter a literal textual `string`, surround it with double quotes, such as `"Megayacht"`. Surrounding that `2` with quotes makes it a `string`, thus aligning it to the left:

You can also enter literal booleans—`true` and `false` (without quotes)—which Notion represents as checked and unchecked `Checkboxes`. `Date` values, on the other hand, cannot be entered as literal values.

#### Constants

Notion offers a couple of keywords for mathematical constants, including `pi` and `e` (the base of the natural logarithm).

#### Property References

All items in a database share property types, such as a `Select` property called "Color," but their values for those properties can be unique, such as "Blue," "Green" and "Yellow." Rather than using literal inputs or constants, which are the same for each database item, property references allow you to use each item's unique values as inputs, thus returning a unique output.

For example, a `Formula` property called "Age" might reference a Birthday property to calculate each person's unique age in years.

To reference another property, you use this format: `prop("[Property]")`. In the example below, the formula `prop("Color")` simply reflects the value of the Color property for each item.

### Actions

Infrequently will you write a formula with a standalone input value, as demonstrated in the above examples. You'll typically prescribe actions to perform on your inputs for the desired calculations, modifications and reformatting.

One common example is automating values for online orders. Using property references, the Tax property multiplies Subtotal by Tax Rate, then Total adds Tax to Subtotal:

To specify actions, you use operators and functions.

#### Operators

An operator is a single character or pair of characters that indicates an action when placed between input values. They fall into three categories: arithmetic, comparison and concatenation.

##### Arithmetic Operators

Arithmetic operators perform calculations on values predominately of the `number` type.

You're familiar with most of them, including the addition operator (`+`), which adds values. The formula `2 + 2` returns `4`. In this example, each `2` is a literal value; therefore, each iteration of the formula returns `4`:

When we use property references rather than literal values, the formula returns a different value for each item of the database. The example below uses the addition operator to return the sum of the Number 1 and Number 2 properties.

``````prop("Number 1") + prop("Number 2")
``````

Of course, the addition operator is just one of many. You can find the full list of arithmetic operators in my Notion Formula Cheat Sheet.

##### Comparison Operators

Comparison operators perform a test on one input value against another. The returned value is of the boolean data type: it's either `true` or `false`, which Notion represents as a checked or unchecked `Checkbox`.

You're likely familiar with the greater than operator (`>`), which tests whether the left input value is greater than the right input value. The example below tests whether the Number 1 property is greater than Number 2.

``````prop("Number 1") > prop("Number 2")
``````

You can see that the Comparison property for Item C is checked because `300 > 50`.

Comparison operators operate on multiple data types; however, the values being compared must be of the same type; otherwise, you'll get a `type mismatch`. In the case of `strings`, variations of `>` and `<` compare the number of characters. In other words, `"Texas" <= "Mississippi"` returns `true` because `5 < 11`. However, `==` (equal) and `!=` (not equal) compare the contents of the `strings`, not just their lengths.

In a database of tasks, a formula can automate a Complete property based each task's status. If the value of the Status property (a property reference) is "Complete" (a literal value), it returns `true` in the form of a checked `Checkbox`; otherwise, it's `false`, or an unchecked `Checkbox`. (This is useful filters and `Rollups`, among other benefits.)

Here's the formula:

``````prop("Status") == "Complete"
``````

You'll find the full list of comparison operators in my Notion Formula Cheat Sheet.

##### Concatenation Operator

You learned above that the `+` character serves as an addition operator when placed between two `numbers`. However, when the same character falls between two textual `strings`, it merges, or "concatenates," them and returns a combined `string`:

`"Carolina" + " " + "Blue"``"Carolina Blue"`

A People database can automate a "Last, First" property by concatenating the Last Name and First Name properties with a comma and space:

``````prop("Last Name") + ", " + prop("First Name")
``````
##### Functions

Functions are like packaged actions to perform on inputs. Some simply replace operators, such as adding, multiplying and comparing. Others offer more advanced operations, such as converting data types, performing complex calculations, and defining conditional rules.

Each function begins with a keyword, which typically indicates its operation, such as `add`. That keyword is followed by parentheses for specifying inputs. These inputs are known as "arguments," and each formula has unique requirements for its arguments, which often include quantity and data type. For functions that accept more than one argument, you separate them with a comma.

In the Formula Window, below the properties and constants, you'll find a full list of functions, each with an icon indicating its primary data type. Hover over any function for its argument requirements and a brief description. You can also click a formula, or a property, to add it to your formula, but most users type them in the composition area. Typing a function's keyword jumps to it in the list and displays its instructional information.

The aforementioned `add()` function accepts two arguments: `numbers` to add or `strings` to concatenate. I use it only to add `numbers`, as the `concat()` function accepts more than two `strings` for merging.

Here's the fully populated function:

``````add(2, 3)
``````

The example above uses the literal inputs `2` and `3` as the arguments for `add()`. Of course, those inputs could instead by property references:

``````add(prop("Number 1"), prop("Number 2"))
``````

Functions generally fall into four categories. Below, we'll explore a few common examples of each, then dive into more advanced functions in subsequent lessons.

Logic Functions

Logic functions make comparisons and define conditions. For every comparison operator, Notion offers a corresponding function. For example, `largerEq()` performs the same operation as `>=`. It takes two arguments and tests whether the first is greater than or equal two the second:

``````largerEq(3, 2)
``````

The `if()` function, which you'll learn about in a subsequent lesson, allows you to specify which actions and inputs to use for various conditions. As you'll see, the primary purpose of comparison operators and functions is to test conditions for `if()`.

Numeric Functions

Numeric functions work with `numbers` and mostly perform calculations.

Like comparison operators, each arithmetic operator has a corresponding function, as we saw above with `add()`.

Here are a few other common numeric functions:

`max()` and `min()`

From the provided `numbers`, these functions identify the maximum or minimum value.

The following example returns the larger value between the Number 1 and Number 2 properties.

``````max(prop("Number 1"), prop("Number 2"))
``````

`round()`

Round a decimal to the nearest integer.

The following example rounds the constant `pi`.

``````round(pi)
``````

`toNumber()`

Convert a textual `string` to a `number` for use in numeric calculations.

The following example converts the String property to a `number`. Notice its right alignment.

``````toNumber(prop("String"))
``````

Because `Rollup` properties always return `strings`, `toNumber()` is useful for converting its value to a `number`.

Text Functions

Text functions generally manipulate textual `strings`.

Like other operators, the concatenation operator has a corresponding function, `concat()`, which merges the `strings` supplied as its arguments:

``````concat("Go", " ", "Tar", " ", "Heels")
``````

Here are some other common text functions:

`contains()`

Accepts two `strings` and tests whether the first one contains the second one, returning a `boolean`:

``````contains("Megayacht", "yacht")
``````

`join()`

Like `concat()`, `join()` merges the supplied `strings`, but it separates them by the first argument. Therefore, we can supply the space character only once to return `"Go Tar Heels"`.

``````join(" ", "Go", "Tar", "Heels")
``````

`format()`

As the reverse of `toNumber()`, the `format()` function converts a `number` to a `string`. This is useful when you want to display a number alongside other text, as in a progress bar: `●●●●●●●○○○ 70%`

The example below converts the Number 1 property to a `string`, as indicated by its left alignment.

``````form(prop("Number 1"))
``````

Date & Time Functions

Date and time functions accept or return values of the `date` type. For use in these functions, a `date` value can originate in three ways:

1. A `Date` Property
Naturally, the value of a `Date` property is of the `date` type, which can be referenced in a formula.
2. The `now()` Function
The widely versatile `now()` function returns the current date and time each time it loads. It takes no arguments. You'll likely use `now()` often, particularly for calculating time elapsed or remaining.
3. The `fromTimestamp()` Function
Any time can be formatted as a Unix Timestamp, which is the number of milliseconds from January 1, 1970. The `fromTimestamp()` function takes a Unix Timestamp (a `number`) as its argument and returns it as a `date`. Because `Rollups` always return `strings`, a retrieved `Date` property is unusable in a date and time function. However, if you retrieve the `Date` property as a Unix Timestamp, you can use `fromTimestamp()` to convert it to a `date`, which can be used in date and time functions. I'll demonstrate this trick in an upcoming lesson.

`dateBetween()` is perhaps the most common date and time function. To determine the time between two dates, it accepts three arguments:

1. `Date` — The later date
2. `Date` — The earlier date
3. `String` — The unit in which to display the interim time, such as `"years"` or `"days"`

With `now()` as the first or second argument, `dateBetween()` is typically used to determine the amount of time elapsed since a date or time remaining until a date.

Here's an example of each:

Calculate Age from Birthday

A person's age is the number of years between `now()` and the person's birthday:

``````dateBetween(now(), prop("Birthday"), "years")
``````

For `dateBetween()` to return the number of days until a future date, such as a task's deadline, the future date is the first argument; `now()` is the second argument; and `"days"` is the third argument:

``````dateBetween(prop("Deadline"), now(), "days")
``````

In the above example, the Deadline property uses the `end()` function to return the end date from the Dates property.

## Sequencing and Nesting

To this point, our formulas have predominately comprised a single operator or function, but many of your functions will include multiple actions. You'll compose them by sequencing and nesting expressions.

When a combination of inputs, operators or functions returns a single value, it is known as an "expression." Here are two examples:

`2 + 3``5`

`add(2, 3)``5`

Because expressions represent a single value, they can serve as input values. That means we can use the two above expressions within larger formulas:

`add(2, 3) + add(2, 3)``10`

`2 + 3 + add(2, 3)``10`

Formulas respect the order of operations, therefore:

`2 + 3 * 2 + 3``11`

`(2 + 3) * (2 + 3)``25`

Here's a more complex example, where the arguments for `multiply()` are `add(1, 2)` and `1 + 2`:

`multiply(add(1, 2), 1 + 2)``9`

When expressions serve as the arguments for "outer" functions, they are "nested." This can make them difficult to decipher, so I like to compose complex formulas within `Code` blocks, with nesting visualized through line breaks and indentations. Here's the above formula:

``````multiply(
1 + 2
)
``````

In order to paste this structure into the Formula Window, you first need to remove line breaks. An easy way is to paste it into your browser's address bar, then re-copy it.

The above examples use literal values, but of course, they could also be property references, which case it's helpful to visualize the third level of nesting:

``````multiply(