Notion Formulas
Learn all the functions Notion has to offer for their databases formulas. Understand syntax, purpose, and practical examples for each. Looking for more formula ideas? Check out the link at the bottom of the article for more database inspiration.
A
add: operator for calculations
Adds two numbers and returns their sum, or combines two text strings.
Formula: add strings
add("Hello", " World")
and: operator for arguments
String two+ arguments together to returns the logical AND between them.
Formula: multiple checkboxes clicked true
prop("Habit 1") and prop("Habit 2") and prop("Habit 3")
abs: function for calculations
Find the absolute value of a number.
Formula: find absolute value of days between today and date
abs(dateBetween(now(), prop("Deadline"), "days"))
C
concat: function for strings
Connect text strings together in one function.
Formula: automate response from form with names
concat("Hello ", prop("First Name"), ", ", "thank you for the sub")
cbrt: function for calculations
Find the cube root of a number.
ceil: function for calculations
Round a number up to the closest integer. For percentages, you can round up a number like 15.3667% to 16%.
Formula: find week number in current month
ceil((date(now()) + day(dateSubtract(now(), date(now()) - 1, "days")) - 1) / 7)
contains: function for arguments
Finds if one argument is contained in another.
Formula: entry contains particular tag
contains(prop("Tags"), "Tag 2")
D
day: function for dates
Find the day (Sun-Sat) for a determined date or the current date ("now()"). Days range from 0-6. 0 is Sunday and 6 is Saturday.
Formula: date lands on weekend
or(day(prop("Date")) == 0, day(prop("Date")) == 6)
divide: function for calculations
Divide two+ numbers to return their quotient.
dateAdd: function for dates
Push dates forward from a defined date or todays' date ("now()"). Date types include "years," "days," "weeks," "quarters," "months," and more.
Formula: push date forward via a select dropdown menu option
dateAdd(prop("Date"), toNumber(prop("Push")), "days")
dateSubtract: function for dates
Push dates backward from a defined date or today's date ("now()"). Date types include "years," "days," "weeks," "quarters," "months," and more.
Formula: find the first day of this month
formatDate(dateSubtract(now(), date(now()) - 1, "days"), "MMMM D, YYYY")
dateBetween: function for dates
Find the length of time between two defined dates or a defined date and today's date. The order of operations should go as follows:
dateBetween(date farthest away, date closer to now, amount, "date type")
Formula: find days between now and an upcoming deadline
dateBetween(prop("Deadline"), now(), "days")
date: function for dates
Find number a date lands on. For example, the date for August 4, 2021 is 4.
E
e: constant for calculations
Base of the natural logarithm. A function best suited for growth equations.
Formula: interest rates and compound growth
prop("Start Amount") * e ^ (prop("Interest Rate") * prop("Years"))
equal: operator for arguments
Finds if two+ arguments are equal.
Formula: find if defined finance goal is met
equal(prop("Balance"), prop("Goal")) ? "Yes! Goal Achieved" : ""
exp: function for calculations
This function replaces any instance where e^x is used. What is inside the function represents the argument x.
Formula: interest rates and compound growth (alternative)
prop("Start Amount") * exp(prop("Interest Rate") * prop("Years"))
end: function for dates
When using a date-range, return the end date. When using a dateBetween function with a date-range, place the end date in the front of the operation.
Formula: days elapsed between a date range
dateBetween(end(prop("Work Time")), start(prop("Work Time")), "days")
empty: operator for arguments
Determine if cell is empty anywhere in a database by wrapping this function around the name of the property.
Formula: fill cell alert
empty(prop("Date"))
F
floor: function for calculations
Round a number down to the closest integer. For percentages, you can round down a number like 15.8667% to 15%.
Formula: find current month's progress
floor(100 * date(now()) / date(dateSubtract(dateAdd(dateSubtract(now(), date(now()) - 1, "days"), 1, "months"), 1, "days"))) / 100
format: function for strings
Format any argument into a text string. In other words, you can convert a numerical value into text.
Formula: combine number and text string
format(2 + 5) + " days"
fromTimestamp: function for dates
Convert a timestamp into a date.
formatDate: function for dates
Format defined date or today's date. Here are some syntax options:
MMMM → January
DD → 05
YYYY → 2021
h:mm a → 7:38 am
Formula: return time an entry is created
formatDate(prop("Created"), "h:mm a")
H
hour: function for dates
Find the hour for a determined date/time or the current time ("now()"). Hours are 0-24.
Formula: find the current time of day (ie. morning)
if(hour(now()) == 0, "", if(hour(now()) < 12, "Morning", if(hour(now()) < 17, "Afternoon", if(hour(now()) < 25, "Evening", ""))))
I
if: operator for arguments
Return a result only if a condition is met. If the condition is not met return a false condition. Two options for syntax:
if(condition, result, false condition)
condition ? result : false condition
Formula: find if deadline lands on today and return string
if(formatDate(now(), "L") == formatDate(prop("Deadline"), "L"), "Due Today", "")
J
join: function for strings
Join a series of strings with one special character.
Formula: combine properties to create an ID
join(":", formatDate(prop("Created"), "L"), prop("Created by"))
L
larger: operator for arguments
Return if one argument is larger than another.
Formula: determine if deadline is overdue and not done
empty(prop("Done")) and formatDate(prop("Deadline"), "L") != formatDate(now(), "L") and now() > prop("Deadline")
largerEq: operator for arguments
Return if one argument is equal to OR greater than another argument.
Formula: return if meeting is today and the current or future time
formatDate(prop("Meeting"), "L") == formatDate(now(), "L") and prop("Meeting") >= now()
length: function for strings
Return the number of characters in a string.
Formula: find number of tags in a multi-select property
if(length(prop("Tags")) > 0, length(replaceAll(prop("Tags"), "[^,]", "")) + 1, 0)
ln: function for calculations
Inverse of the exponential function ("exp()"). Read more about the ln() function.
log10: function for calculations
Base 10 logarithm of a number.
Formula: find number of digits in a number
ceil(log10(prop("Number")))
log2: function for calculations
Base 2 logarithm of a number.
M
max: function for arguments
Find the maximum value among two+ numbers.
min: function for arguments
Find the minimum value among two+ numbers.
mod: operator for calculations
Returns the remainder of the quotient of two numbers. The best use of this function is to return date and time ranges between two dates.
Formula: return hours and minutes elapsed in the same day
format(mod(dateBetween(end(prop("Work Time")), start(prop("Work Time")), "hours"), 24)) + "h " + format(mod(dateBetween(end(prop("Work Time")), start(prop("Work Time")), "minutes"), 60)) + "m"
month: function for dates
Find the month for a determined date or the current date ("now()"). Months range from 0-11.
Formula: determine if deadline is in the current month
month(now()) == month(prop("Deadline"))
multiply: funtion for calculations
Find the product of two numbers.
minute: function for dates
Find the minute for a determined date/time or the current date/time ("now()"). Minutes range from 0-59.
Formula: find progress of current hour
ceil(100 * minute(now()) / 60) / 100
N
not: operator for arguments
Return if argument is not meeting a condition.
Formula: true if one checkbox is ticked and another is not
prop("Prepped") and not prop("Re-schedule")
now: function for dates
Timestamp for current date and time.
Formula: return how many days have passed since entry was created
format(abs(dateBetween(prop("Date Created"), now(), "days"))) + " days ago"
O
or: operator for arguments
String two+ arguments together to returns the logical OR between them.
P
pow: operator for calculations
Use the exponent power for calculations. Can also be written as ‘x^y’.
pi: constant for calculations
Ratio of a circle’s circumference to its diameter. 3.14159 ….
R
replace & replaceAll: function for strings
Define one character to remove or replace.
Step 1: define expression → replace("hello", "ello", "i")
Step 2: define characters to replace → replace("hello", "ello", "i")
Step 3: replace defined character with another → replace("hello", "ello", "i")
Result: hi
The replaceAll function replaces all instances of the defined character.
Formula: extract last word from a string
replace(prop("Person"), ".* ", "")
round: function for calculations
Rounds number to the nearest integer. Here are different methods for rounding numbers:
round() → round to nearest integer
floor() → round to lowest integer
ceil() → round to highest integer
Formula: round number to nearest hundreth
round(100 * 1.565555) / 100
S
smaller: operator for arguments
Return if one argument is smaller than another.
smallerEq: operator for arguments
Return if one argument is equal to OR less than another argument.
Formula: return if number is within defined budget
prop("Spent") <= prop("Budget")
slice: function for strings
Extract parts of a string from a defined start and end point.
Formula: progress bar
if(not empty(prop("Read")) and not empty(prop("Total Pages")), slice("■■■■■■■■■■", 0, floor(10 * prop("Read") / prop("Total Pages"))) + slice("⃞⃞⃞⃞⃞⃞⃞⃞⃞⃞", 0, 10 - floor(10 * prop("Read") / prop("Total Pages"))) + " " + format(floor(100 * prop("Read") / prop("Total Pages"))) + "%", "")
start: function for dates
When using a date-range, return the start date. When using a dateBetween function with a date-range, place the end date in the front of the operation.
Formula: find time elapsed between a date range (days, hours, minutes)
format(dateBetween(end(prop("Date")), start(prop("Date")), "days")) + "d " + format(dateBetween(end(prop("Date")), start(prop("Date")), "hours") % 24) + "h " + format(dateBetween(end(prop("Date")), start(prop("Date")), "minutes") % 60) + "m"
sign: function for calculations
Determine whether a number is positive negative or zero.
negative number = -1
zero = 0
positive number = 1
sqrt: function for calculations
Find square root of a number.
subtract: function for calculations
Subtracts two numbers and returns the difference.
T
toNumber: function for strings
Convert string to a number.
Formula: extract number from string
toNumber(prop("Push"))
test: operator for arguments
Test if a string matches or contains a defined character.
timestamp: function for dates
Return numerical timestamp from a date. Great for creating unique IDs for database entries.
Formula: shortened timestamp of entry's created time
timestamp(prop("Created")) / 1000
U
unaryPlus: operator for arguments
Return a value for an argument.
true = 1
false = 0
Formula: find percentage of habit progress
round(100 * (unaryPlus(prop("Habit 1")) + unaryPlus(prop("Habit 2")) + unaryPlus(prop("Habit 3"))) / 3) / 100
unaryMinus: operator for arguments
Convert positive numbers to negative numbers, and negative numbers to positive.
unequal: operator for arguments
Find if one argument does NOT match another. Can also be written as ‘x != y’.
Formula: find if date is in the past
prop("Date") < now() and formatDate(prop("Date"), "L") != formatDate(now(), "L")
Y
year: function for dates
Find the year of a defined date or the current date.
Formula: current year’s progress
floor(100 * if(year(now()) % 4 == 0, toNumber(formatDate(now(), "DDD")) / 366, toNumber(formatDate(now(), "DDD")) / 365)) / 100