Formulas

In a Notion database, you can add a formula property that lets you run all kinds of calculations and functions based on other properties. Just like in Excel or Google Sheets, you can use formulas in Notion to find sums and arrive at many other helpful values ⚗️

We know this can be complicated, so don't hesitate to reach out to us if we can help.

Formula terminology Terminology in action Writing a formula Formula menu Common examples Automatic checkbox Date calculations Rounding formulas Add weekday to a date Overdue alert Troubleshooting FAQs Related guides

Formula terminology

The hardest thing about writing formulas is understanding all the terminology! 😅 Here are the most common words you'll see when using formulas in Notion and what they mean:

Function: A relation from a set of inputs to a set of possible outputs where each input is related to exactly one output.

Syntax: Refers to the order of letters and terms in your formula to return the right value.

Argument: Refers to any input in a function, like a property.

String: Most often refers to text (i.e. a string of letters next to each other). Strings are always wrapped in quotation marks in formulas.

A substring refers to a segment of a larger string. "No" is a substring of "Notion".

Boolean: A boolean is either true or false.

Concatenate: Combines two strings together.

Example: concat("add", "text") would yield "addtext"

Terminology in action

Here's an example to tie all of the above together: concat("add", "text")

concat is the function. It applies a certain calculation or action to the inputs contained in its parentheses.

"add" and "text" are arguments in that function.

Each argument is text (otherwise known as a string).

Functions can use a variety of different arguments, like checkboxes (also known as booleans), numbers or dates.

Function syntax requires strings to be wrapped in quotes and its arguments to be contained within parentheses.

Writing a formula

First, in your database, add a new property, give it a name and choose Formula from the Property Type menu.

To tell a formula to do something with a property, type a function that will use that property.

Properties operate a lot like variables in formulas. Use the names you've given them.

You can type functions just the way you would into a digital calculator (remember those?). Mind your parentheses and quotation marks around your properties!

Example: How many years to 21?

Let's say you have a number property called Age in your database, and you want your formula property to show how many years away people's ages are from 21.
Type 21 - prop("Age") to return a result of 21 minus whatever is in the age property for each person in your database.

Formula menu

Whenever you edit a formula property, the formula window pops up. Its left sidebar menu has several sections:

Properties: Lists all the properties being used in your database. Clicking on any of these inserts them into your formula with proper formatting.

Constants: Common constants like pi. Click on any of them to add them into your formula.

Operators: Simple calculations you can run. The icons to their left show you which types of properties they work with. For instance, subtraction and addition work on number properties.

Functions: More complex, pre-defined formulas you can run in Notion. The icons to their left also show you what types of properties they work with, too.

Start typing any of these in the text box at the top of the window to search for what you need.

You can also scroll up and down with your arrow keys.

🎒

Tip: Hover over any of the terms in the sidebar for a full definition of how they work and an example of how they can be used.

Example: Days left before deadline

Here, we have a task database with deadlines. Our goal is figure out how many days are left between now and these various deadlines.
For every task in your database, enter a deadline in your date property.
Then add a new property and choose Formula from the Property Type menu. Give it the name "Days Left."
In your table, click on any cell in your formula column to bring up the formula window.

In the text box provided, type date to bring up date-related commands. dateBetween() looks helpful — it should return the time between two dates in our desired unit of time.

According to this function's instructions, you need to give it two dates and some text: your deadline date, the date right now, and the unit you want the range between these two dates to be expressed in (years, months, days, etc.).

Insert your deadline date into the dateBetween() function by clicking on it under Properties at the top of your formula sidebar, or typing it in: prop("Deadline")

Now our formula looks like dateBetween(prop("Deadline")). Let's search for a way to mention today's date. now() looks useful because it should return the current date.

Here's how to complete the function:

First, add a comma to tell the function we want to find the amount of time between the deadline date and now: dateBetween(prop("Deadline"), now())
Next, define the unit of time we want our result to be displayed in, per our function's instructions: dateBetween(prop("Deadline"), now(), "days")
Last, click Done to apply the function to your database.
Done! Now you know how many days remain before your deadlines 🙌

Common examples

We've aggregated a few common formula examples and how to write them here 👇

Automatic checkbox

You can write a formula so a checkbox in your database is either checked or unchecked based on another property's value.

Goal: Check the box if Priority is empty or contains the word "Important."

Formula: or(empty(prop("Priority")), prop("Priority") == "Important")

Demo

Name

Priority

Has Value?

Blank value

Non-matched value

Not important

Matched value

Important

This one also works with the Select property in databases. In this example, a box is checked if "Option 1" is put in the Select property.

equal(prop("Select"), "Option 1")

Demo

Name

Select

Checkbox

Thing One

Option 1

Thing Two

Option 2

Date calculations

Using a date property in your database, you can calculate all kinds of ranges.

Demo

Name

Start Date

End Date

Years Employed

Years Remaining

Formatted Text

Douglas

Jul 18, 2015

Jul 18, 2030

NaN years, undefined months

Goal: Display the number of years Doug has been employed so far under "Years Employed".

Formula: dateBetween(now(), prop("Start Date"), "years")

Goal: Display the number of years Doug has left to work under "Years Remaining".

Formula: dateBetween(prop("End Date"), now(), "years")

Goal: Display how long Doug has left to work in years and months under "Formatted Text".

Formula: concat(format(prop("Years Employed")), " years, ", format(dateBetween(prop("End Date"), now(), "months")), " months")

Rounding formulas

These formulas can be used to round numbers in different ways.

Round to whole number: Wrap your formula with round()

Example: round(prop("One") / prop("Two"))

Round to one decimal: Wrap your formula with round(*10)/10

Example: round(prop("One") / prop("Two") * 10) / 10

Round to two decimals: Wrap your formula with round(*100)/100

Example: round(prop("One") / prop("Two") * 100) / 100

Add weekday to a date

Take a date and automatically identify the day of the week in a formula property.

formatDate(prop("Date"), "dddd, MMMM D, YYYY")

Overdue alert

Automatically check a box if you've passed a due date. Helpful for personal CRMs when you set a date when you next want to contact a person and you run over it.

prop("Due Date") < now()

🎒

Tip: We'll continue to collect useful formulas from our team and community. Stay tuned! If you have one to share, send it to us at team@makenotion.com.

Troubleshooting

There are a couple common error messages you might run into when using formulas:

Type mismatch: prop("Number") is not a Text

This means the formula you're using only wants to use text properties.
As a workaround, try wrapping your number property with format().
Example: prop("Number") + prop("Text") will return the error. Change it to: format(prop("Number")) + prop("Text")

Too few arguments in function if

This means you are probably missing an "else" statement.
Try adding , "") to the end of your formula to add "else empty".
Example: if(prop("1") == "this", "that") will return the error.

Change it to: if(prop("1") == "this", "that", "")

FAQs

Any way to Group By a relation or formula property in board?

Not currently 😓It’s a legit use case though, and definitely something we want to support in the future.

Related guides

Something we didn't cover? Message us in the app by clicking ? at the bottom right on desktop (or in your sidebar on mobile). Or email us at team@makenotion.com ✌️