# Formulas

Mar 14 '19 · 6 min read · 2145 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. 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. • 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.
• 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")`

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()`

# 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