What are Notion Formulas?

Formulas in Notion allow you to perform calculations on data inside your databases. Formulas are a powerful way to manipulate and analyze your data, and they can be used to calculate sums, averages, counts, and other values based on your data.

To start creating formulas in Notion, follow these steps:

Go to the page where your database is located, and create a database
In the database editor, select the “Formulas” tab, and then add a formula property
In the formula editor, enter the expression for your formula. You can use the editor to insert values and operators and to create your formula.
When you are finished, click on the “Save” button to save your formula. Important: the formula will not save if it is incorrect!

After you have created a formula property in your database, it will be added to your database as a new column. You can use the formula column to sort and filter your data, and you can also use it in other formulas and calculations.

Database formulas are a powerful way to analyze and manipulate your database data in Notion, and they can be used to create complex and sophisticated analyses of your data.

What formulas can you use in Notion? Formula Cheat Sheet

Constants

A constant is a fixed value that does not change. Constants can be used in formulas to represent a fixed value that you want to use in your calculations.

Notion allows the following constants:

The number e (the base of the natural logarithm) e == 2.718281828459045
The number Pi (the ratio of a circle’s circumference to its diameter) pi ==3.14159265359
True and False statements

Operators

if: Switches between two options based on another value. if(prop("X") == true, "yes", "no")
add: Adds two numbers and returns their sum, or concatenates two strings. add(1, 3) == 4 or add("add", "text") == "addtext"
subtract: Subtracts two numbers and returns their difference. subtract(4, 5) == -1
multiply: Multiplies two numbers and returns their product. multiply(2, 10) == 20
divide: Multiplies two numbers and returns their product. divide(12, 3) == 4
pow: Returns base to the exponent power, that is, baseexponent. pow(2, 2) == 4 or pow(2, 6) == 64
mod: Divides two numbers and returns their remainder.. mod(3, 2) == 1
unaryMinus: Negates a number. unaryMinus(42) == -42
unaryPlus: Converts its argument into a number. unaryPlus(true) == 1 or unaryPlus("42") == 42
not: Returns the logical NOT of its argument. not(true) == false or not(false) == true
and: Returns the logical AND of its two arguments. and(true, true) == true or and(true, false) == false
or: Returns the logical OR of its two arguments. (false, false) == false or (true, false) == true
equal: Returns true if its arguments are equal, and false otherwise. equal(false, not true) == true equal(false, true) == false
unequal: Returns false if its arguments are equal, and true otherwise. (true != not false) == false
larger: Returns true if the first argument is larger than the second. 5 > 3 == true
largerEq: Returns true if the first argument is larger than or equal to than the second. 5 >= 4 == true or 4 >= 4 == true
smaller: Returns true if the first argument is smaller than the second. 10 < 8 == false or 8 < 18 == true
smallerEq: Returns true if the first argument is smaller than or equal to than the second. 10 <= 8 == false or 9 <= 18 == true or 10 <= 10 == true

Functions

concat: Concatenates its arguments and returns the result. concat("dog", "go") == "doggo"
join: join("-", "a", "b", "c") == "a-b-c"
slice: Extracts a substring from a string from the start index (inclusively) to the end index (optional and exclusively). slice("Hello world", 1, 5) == "ello slice("notion", 3) == "ion""
length: Returns the length of a string. length("Hello world") == 11
format: Formats its argument as a string. format(42) == "42"
toNumber: Parses a number from text. toNumber("42") == 42 toNumber(false) == 0
contains: Returns true if the second argument is found in the first.contains("notion", "ion") == true
replace: Replaces the first match of a regular expression with a new value. replace("1-2-3", "-", "!") == "1!2-3"
replaceAll: Replaces all matches of a regular expression with a new value. replaceAll("1-2-3", "-", "!") == "1!2!3"
test: Tests if a string matches a regular expression. test("1-2-3", "-") == true
empty: Tests if a value is empty. empty("") == true
abs: Returns the absolute value of a number. abs(-3) == 3
cbrt: Returns the cube root of a number. Returns the cube root of a number.
ceil: Returns the smallest integer greater than or equal to a number. ceil(4.2) == 5
exp: Returns E^x, where x is the argument, and E is Euler’s constant (2.718â€¦), the base of the natural logarithm. exp(1) == 2.718281828459045 exp(2) == 7.389056098931
floor: Returns the largest integer less than or equal to a number. floor(2.8) == 2
ln: Returns the natural logarithm of a number. ln(e) == 1
log10: Returns the X property for each entry. prop("X") == true
log2: Returns the base 2 logarithm of a number. log2(64) == 6
max: Returns the largest of zero or more numbers. max(5, 2, 9, 3) == 9
min: Returns the smallest of zero or more numbers. min(4, 1, 5, 3) == 1
round: Returns the value of a number rounded to the nearest integer. round(4.4) == 4
sign: Returns the sign of the x, indicating whether x is positive, negative or zero. sign(4) == 1
sqrt: Returns the positive square root of a number. sqrt(144) == 12
start: Returns the start of a date range. start(prop("Date")) == Feb 2, 1996
end: Returns the end of a date range. end(prop("Date")) == Feb 2, 1996
now: Returns the current date and time. now() == Feb 2, 1996
timestamp: Returns an integer number from a Unix millisecond timestamp, corresponding to the number of milliseconds since January 1, 1970. timestamp(now()) == 1512593154718
fromTimestamp: Returns a date constructed from a Unix millisecond timestamp, corresponding to the number of milliseconds since January 1, 1970. fromTimestamp(2000000000000) == Tue May 17 2033
dateAdd: Add to a date. The last argument, unit, can be one of: “years”, “quarters”, “months”, “weeks”, “days”, “hours”, “minutes”, “seconds”, or “milliseconds”. dateAdd(date, amount, "years")
dateSubtract: Subtract from a date. The last argument, unit, can be one of: “years”, “quarters”, “months”, “weeks”, “days”, “hours”, “minutes”, “seconds”, or “milliseconds”. dateSubtract(date, amount, "years")
dateBetween: Returns the time between two dates. The last argument, unit, can be one of: “years”, “quarters”, “months”, “weeks”, “days”, “hours”, “minutes”, “seconds”, or “milliseconds”. dateBetween(date, date2, "years")
formatDate: Format a date using the Moment standard time format string. format(42) == "42"
minute: Returns an integer number, between 0 and 59, corresponding to minutes in the given date. minute(now()) == 45
hour: Returns an integer number, between 0 and 23, corresponding to hour for the given date. hour(now()) == 17
date: Returns an integer number corresponding to the day of the week for the given date: 0 for Sunday, 1 for Monday, 2 for Tuesday, and so on. day(now()) == 3
month: Returns an integer number, between 0 and 11, corresponding to month in the given date according to local time. 0 corresponds to January, 1 to February, and so on. month(now()) == 11
year: Returns a number corresponding to the year of the given date. year(now()) == 2023
id: Returns a unique string id for each entry. id() == "083ee30ce5a048dfadf55f1944688405"

How are Notion formulas different from Formulas in Excel?

Notion formulas are similar to formulas in Excel in that they allow you to perform calculations and manipulate data within your databases. However, there are a few key differences between the two:

Notion formulas are designed to be used within the context of a page or database, whereas Excel formulas are typically used within a spreadsheet.
Moreover, Excel formulas are cell-based, while Notion formulas are column based. What does this mean? While in Excel you can reference individual cells, in Notion you can only reference other properties. The formulas will apply identically to all rows in a column.

Excel formulas are therefore much more flexible and powerful. Still, there are many interesting things you can do with Notion formulas to supercharge your Notion workspace:

Here are 8 practical use cases for Notion Formulas

1. Calculate the priority of a task (Eisenhower Matrix)

The Eisenhower Matrix is a tool used to prioritize tasks by distinguishing between urgent and important tasks. It is named after former U.S. President Dwight D. Eisenhower, who is said to have used a similar system to manage his workload. The matrix consists of four quadrants:

urgent and important,
important but not urgent,
urgent but not important,
neither urgent nor important.

Tasks are placed in one of the quadrants based on their level of importance and urgency, and this can help a person decide which tasks to prioritize and which ones to delegate or eliminate. The idea behind the Eisenhower Matrix is that focusing on important tasks can help a person achieve their goals, while ignoring or delegating tasks that are not important or urgent can help them avoid distractions and stay productive.

Notion formulas allow you to simply find the right quadrant by stating if a task is urgent or important by using the following formula:

$if(prop(“Important”), if(prop(“Urgent”), “Do”, “Schedule”), if(prop(“Urgent”), “Delegate”, “Eliminate”))$

2. Calculate the number of days between a date range

This can be for example useful if you need to know the time between the start and end day of a project. use the following formula for calculating the days:

$dateBetween(prop(“Date 2”), prop(“Date 1”), “days”)$

You can also change â€œdaysâ€ to â€œyearsâ€, â€œmonthsâ€, â€œweeksâ€, or â€œhoursâ€ if you need a different measurement unit.

3. Calculate overdue days

This is useful if you want to see how many days a project or a bill is overdue.

Use this formula from Red Gregory to get the respective status update as shown in the screenshot.

if(formatDate(prop(“Deadline”), “MMM DD, YYYY”) == formatDate(now(), “MMM DD, YYYY”), “Due Today âœ…”, if(dateBetween(prop(“Deadline”), now(), “days”) > 0, format(dateBetween(prop(“Deadline”), now(), “days”) + 1) + ” Days Remaining”, if(dateBetween(prop(“Deadline”), now(), “days”) > -1, “Due Tomorrow ðŸ”œ”, if(dateBetween(prop(“Deadline”), now(), “days”) < 0, format(abs(dateBetween(prop(“Deadline”), now(), “days”))) + ” Days Past Due â•ï¸”, “”))))

4. Calculate total revenue (or profit)

Calculate your revenue by multiplying sold items by the price.

prop(“Sold Items”) * prop(“Net Price”)

To calculate the profit, you need to take the total costs into account.

prop(“Sold Items”) * prop(“Cost per Item”)

and subtract them from your revenue:

prop(“Revenue”)-prop(“Costs”)

5. Calculate the age from a birthday

Easily calculate the age of a person in Notion with the following dateBetween-formula:

dateBetween(now(), prop(“Birthday”), “years”)

6. Split full name into the first and second name

For the first name, we use the replaceAll-formula to replace all the text after the first space with empty space.

replaceAll(prop(“Full Name”), “[ ].+”, “”)

This also works with the second name the other way around:

replaceAll(prop(“Full Name”), “.+[ ]“, “”)

7. Calculate the number of characters from a text field

This is for example important if you are using Notion to write something, where you have a character limit, for example, a tweet on Twitter.

In this example, we are calculating the number of characters of the names from the previous formula.

length(prop(“Full Name”)) – 1

Why -1 at the end? Simply because we don’t want to count the space between the names for the calculation.

8. Calculate the number of words from a text field

Calculating the number of characters is easy, but in many cases, it would be much more interesting to see the number of words written. How can you do that?

By using a workaround. Each word is separated by a space, if we calculate the number of spaces in the text and add 1, we can obtain the number of words.

For doing so, we first calculate the number of characters without spaces

length(replaceAll(prop(“Text”), “[ ]”, “”)

and subtract the result with the total number of characters:

length(prop(“Text”))

To get the correct word count, we need to add 1 at the end. The whole formula looks like that:

length(prop(“Text”)) – length(replaceAll(prop(“Text”), “[ ]”, “”)) + 1

Conclusion

This is how you are working with formulas in Notion. Do you want to calculate something in Notion, but have problems finding the right formula or outputting the right results? Share your challenge in the comments below!

Notion Formulas Explained – Supercharge Your Notion Setups