Formulas

Camille Ricketts
Mar 14 Β· 6 min read Β· 51 views
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

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 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

NamePriorityHas Value?
Blank value  
Non-matched valueNot important 
Matched valueImportant 
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

NameSelectCheckbox
Thing TwoOption 2 
Thing OneOption 1No

Date calculations

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

Demo

NameStart DateEnd DateYears EmployedYears RemainingFormatted Text
Douglas    
  • 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 ✌️
Updating...

Share on