Formula is a powerful yet underutilized property in Notion databases. This introductory guide covers the fundamentals of formulas, then details three practical examples for business workspaces.
You can duplicate those examples to your own workspace from Notion Market.
Here’s what you’ll learn:
What’s Notion’s Formula property?
Date properties, the values of
Formula properties are generated automatically. You compose a formula once, and it executes for each item in the database.
Typically, that formula will reference other properties in the database. Because the values of those properties can be different for each item, the output of the formula can also be different for each item.
For example, in a database of people, a
Formula can determine each person’s age by calculating the time between the Birthday (
Date) property and the current date (
now()). Because each birthday can be different, each age can also be different.
Why use formulas?
The utility of formulas is virtually endless but falls into two high-level categories:
Merge and reformat other properties.
- You can create a Full Name property by merging First Name and Last Name.
- You can give a date virtually any format, expanding the limited options offered natively by Notion.
Calculate new values from other properties.
The age calculation was an example of this. Another simple example is calculating the total price of an ecommerce order from properties like Price, Quantity, Tax and Shipping.
The anatomy of a formula.
To compose a formula, click any cell within the
Formula property column. Your formula will consist of one or more of the below elements. For a deeper dive into these components, see Meet Notion’s Formula Property.
Values are the inputs of your formula — the items you calculate or reformat. They can be input directly, such as the number
2, or they can be the value of another property, such as Birthday (
Date). Using another property is known as a reference, which you create in this format:
Remember, a formula iterates for each item in the database, so each item’s iteration will reference the properties for that item.
Each value is a particular data type, such as
boolean (true or false) or
string (text). Your formulas will require particular data types, so it’s important to remain mindful of them. Learn more in The Importance of Data Types.
Operators are placed between values and specify the actions to perform on those values. You’re familiar with arithmetic operators, such as
-. To divide and multiply, you use
You can also use operators to compare values, such as
<. To test whether values are equal or unequal, use
In some cases, an operator’s action depends on your data types (
dates, etc.). When used with
+ character is an addition operator; it adds the numbers:
2 + 2 →
4. When used with
strings (text), however, it becomes a concatenation operator; it merges the values:
"LeBron" + " " + "James" →
"LeBron James". (When used in formulas, text strings are placed in quotation marks.)
Placed between a
string and a
+ character will result in a type mismatch. This is why it’s important to remain mindful of value types.
Functions are predefined actions to perform on its input values, which are known as its arguments. They’re formatted as a keyword followed by arguments in parentheses, separated by a comma. For example, the
add() function with the arguments of
3 is written as
As you might suspect, the
add() function returns of the sum of its arguments. The formula
add(2, 3) returns the same value as
2 + 3.
Notion offers a variety of formulas for every value type. For a comprehensive list of formulas and operators, see our Notion Formula Cheat Sheet.
Calculate days until a deadline.
In a variety of circumstances, it’s helpful to count the days since a date or until a date. For example, you may want to calculate the days since your last check-in with each customer. For tasks, it’s often helpful to count down the days until the deadline. You can then configure views to surface items needing attention, as we see in Bulletproof Tasks.
Both of these examples are easily accomplished with a formula using the
dateBetween() function, which takes three arguments:
- The first date
- The second date
- The unit, such as “hours,” “days,” or “years”
In a database of tasks, a Days Remaining (
Formula) property calculates the days between the Deadline (
Date) and the current time, which is written as the
now() function with no arguments:
dateBetween( prop("Deadline"), now(), "days" )
Calculate product pricing.
A database of products may have a combination of these
number properties, some of which can be automated using formulas:
- Product (
- Cost (
- Markup % (
- Markup (
- Price (
- Quantity (
- Subtotal (
- Tax (
- Shipping (
- Total (
Cost, Quantity and Shipping are unique to each product, so they’re manually entered
By using a
Formula property for Markup % and Tax, we can automatically use the same value for each product.
Markup multiplies Cost by Markup %:
prop("Cost") * prop("Markup %")
Price adds Markup to Cost:
prop("Cost") + prop("Markup")
Subtotal multiplies Price by Quantity:
prop("Price") * prop("Quantity")
Total multiplies Subtotal by Tax, then adds Shipping:
prop("Subtotal") * prop("Tax") + prop("Shipping")
Merge first and last names.
For a variety of reasons, you may wish to have independent properties for first and last names. I like to sort by last name and use first names with mail merge tools. Rather than entering each contact’s first name, last name and full name, you can use a formula to generate the full name automatically.
Because Notion does not yet support formulas in the
Title property, you can use a “Title Generator” property to produce the full name, then quickly copy it to the
Title. Hopefully we’ll see formula-generated
Titles soon. ?
Titleproperty is called Full Name.
- First Name and Last Name are both
- The Title Generator is a
For the formula, we want to combine the First Name and Last Name properties, separated by a space. We could simply use the
+ operator, as previously demonstrated, but for the sake of learning, let’s take an alternative approach. The
join() function merges two
strings with a specified delimiter. It accepts three arguments:
- The delimiter
- The first
- The second
In our example, the two
strings are the values of the First Name and Last Name properties, and the delimiter is a space. Therefore, our full formulas is:
join( " ", prop("First Name"), prop("Last Name") )
For each contact, you can manually enter First Name and Last Name, then copy the automatically generated value of Title Generator to Full Name.
In Part 2, I’ll cover how to populate first name and last name automatically when the full name is manually entered in the
Questions? Tweet @WilliamNutt.