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.
Unlike Text, Number and 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.
The utility of formulas is virtually endless but falls into two high-level categories:
For example:
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.
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: prop("Birthday").
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 number, date, 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 + and -. To divide and multiply, you use / and *, respectively.
You can also use operators to compare values, such as > and <. To test whether values are equal or unequal, use == and !=.
In some cases, an operator's action depends on your data types (numbers, dates, etc.). When used with numbers, the + 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 number, the + 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 2 and 3 is written as add(2, 3).
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.
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:
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" )
A database of products may have a combination of these number properties, some of which can be automated using formulas:
Title)Number)Formula)Formula)Formula)Number)Formula)Formula)Number)Formula)Cost, Quantity and Shipping are unique to each product, so they're manually entered numbers.
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")
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. ?
Title property is called Full Name.Text properties.Formula.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:
stringstringIn 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 Title property.
Questions? Tweet @WilliamNutt.