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.
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 Rollup.
RollupWhen 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.
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.
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:
NumberString (Text)Boolean (true or false)DateRegardless 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.
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.
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.
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.
Notion offers a couple of keywords for mathematical constants, including pi and 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 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 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.
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 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:
Date PropertyDate property is of the date type, which can be referenced in a formula.now() Functionnow() 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() FunctionfromTimestamp() 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:
Date — The later dateDate — The earlier dateString — 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")
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")
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 → 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(
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.