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.
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.
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:
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'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:
Formulas also provide creative methods for sidestepping some of Notion's unintended obstacles. One common example is "rolling up" a
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
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
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.
Done saves your formula. It executes and returns a value for each item in the database.
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
Date properties display
booleans; and most other properties are textual
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
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.
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
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.
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:
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.
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—
false (without quotes)—which Notion represents as checked and unchecked
Date values, on the other hand, cannot be entered as literal values.
Notion offers a couple of keywords for mathematical constants, including
e (the base of the natural logarithm).
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.
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.
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 perform calculations on values predominately of the
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
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 perform a test on one input value against another. The returned value is of the boolean data type: it's either
false, which Notion represents as a checked or unchecked
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
< compare the number of characters. In other words,
"Texas" <= "Mississippi" returns
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.
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
"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 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.
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:
The example above uses the literal inputs
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 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:
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
Numeric functions work with
numbers and mostly perform calculations.
Like comparison operators, each arithmetic operator has a corresponding function, as we saw above with
Here are a few other common numeric functions:
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 a decimal to the nearest integer.
The following example rounds the constant
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.
Rollup properties always return
toNumber() is useful for converting its value to a
Text functions generally manipulate textual
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:
strings and tests whether the first one contains the second one, returning a
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")
As the reverse of
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:
The example below converts the Number 1 property to a
string, as indicated by its left alignment.
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:
Dateproperty is of the
datetype, which can be referenced in a formula.
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.
fromTimestamp()function takes a Unix Timestamp (a
number) as its argument and returns it as a
strings, a retrieved
Dateproperty is unusable in a date and time function. However, if you retrieve the
Dateproperty 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:
Date— The later date
Date— The earlier date
String— The unit in which to display the interim time, such as
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")
Display Days Until a Deadline
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.
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 →
add(2, 3) →
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) →
2 + 3 + add(2, 3) →
Formulas respect the order of operations, therefore:
2 + 3 * 2 + 3 →
(2 + 3) * (2 + 3) →
Here's a more complex example, where the arguments for
add(1, 2) and
1 + 2:
multiply(add(1, 2), 1 + 2) →
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") )
Questions? Tweet @WilliamNutt.