Notion VIP Logo
BulletproofA-to-ZConsultingThe Streamline
0
Subscribe

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:

Notion Formula Calculation

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:

Notion Formula Modification

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:

Notion Format with Formula

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.

Notion Workaround with Formula

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.

Notion Formula Window

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:

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:

Notion Data Type Alignment

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 Type Mismatch

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:

Notion Literal Numbers

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:

Notion Literal String

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).

Notion Constants

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.

Property References with Notion Formulas

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:

Order Calculations with Notion Formulas

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:

Add Literal Values with Notion Formulas

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")
Add Property References with Notion Formulas

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.

Compare Numbers with Notion Formulas

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"
Compare Strings with Notion Formulas

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")
Reorder Strings with Notion Formulas
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)
Notion's add() Function

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"))
add() with Property Reference in Notion

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)
Notion's largerEq() Function

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"))
Notion's max() Function

round()

Round a decimal to the nearest integer.

The following example rounds the constant pi.

round(pi)
Notion's round() Function

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"))
Notion's toNumber() Function

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")
Notion's concat() Function

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")
Notion's contains() Function

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")
Notion's join() Function

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"))
Notion's format() Function

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")
Calculate Age in Notion

Display Days Until a Deadline

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")
Calculate Days until a Deadline with a Notion Formula

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 + 35

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 + 311

(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(
	add(1, 2),
	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(
	add(
		prop("Number 1"),
		prop("Number 2")
	),
	prop("Number 1") + prop("Number 1")
)
Nested Formulas in Notion

Questions? Tweet @WilliamNutt.

All-in on
the all-in-one
productivity app.
Subscribe →