Welcome to a reference guide to Notion Formulas. Hopefully you find this useful!
Properties
To refer to a different field property, use the prop("Field Name") syntax.
prop("Name")
This will return, My Entry as shown in the example above.
Constants
There are a few available constants within the formulas:
e
The base of the natural logarithm. This is also known as Eulers Number or Napier's Constant. The value is 2.718281828459045.
So when would we use something like this?
- Compound Interest
pi
The ratio of a circle's circumference to its diameter.
true
A simple, always true, variable.
false
A simple, always false, variable.
Operators
Logic
if
Switches between two options based on another value.
Syntax
boolean ? value : value
if(boolean, value, value)Examples
true ? 1 : -1 == 1 if(false, "yes", "no") == "no"
Number
add
Adds two numbers and returns their sum, or concatenates two strings.
Syntax
number + number
text + text
add(number, number)
add(text, text)Examples
3 + 4 = 7 add(1, 3) == 4 "add" + "text" == "addtext"
divide
Divides two numbers and returns their quotient.
Syntax
number / number
divide(number, number)Examples
12 / 4 == 3 divide(12, 3) == 4
mod
Divides two numbers and returns their remainder.
Syntax
number % number
mod(number, number)Examples
7 % 5 == 2 mod(3, 3) == 0
multiply
Multiplies two numbers and returns their product.
Syntax
number * number
multiply(number, number)Examples
6 * 9 == 54 multiply(2, 10) == 20
pow
Returns base to the exponent power, that is, baseexponent.
Syntax
number ^ number
pow(number, number)Examples
5 ^ 3 == 125 pow(2, 6) == 64
subtract
Subtracts two numbers and returns their difference.
Syntax
number - number
subtract(number, number)Examples
3 - 1 == 2 subtract(4, 5) == -1
unaryMinus
Negates a number.
Syntax
- number
unaryMinus(number)Examples
-1 + 2 == 1 unaryMinus(42) == -42
unaryPlus
Converts its argument into a number.
Syntax
+ value
unaryPlus(value)Examples
+ "42" == 42 unaryPlus(true) == 1
Conditional
and
Returns the logical AND of its two arguments.
Syntax
boolean and boolean
and(boolean, boolean)Examples
true and false == false and(true, true) == true
equal
Returns true if its arguments are equal, and false otherwise.
Syntax
value == value
equal(value, value)Examples
(3 * 5 == 15) == true equal(false, not true) == true
larger
Returns true if the first value is larger than the second.
Syntax
number > number
date > date
text > text
boolean > boolean
larger(number, number)
larger(date, date)
larger(text, text)
larger(boolean, boolean)Examples
5 > 3 == true
largerEq
Returns true if the first value is larger than or equal to the second value.
Syntax
number >= number
date >= date
text >= text
boolean >= boolean
largerEq(number, number)
largerEq(date, date)
largerEq(text, text)
largerEq(boolean, boolean)Examples
5 >= 3 == true 4 >= 4 == true
not
Returns the logical NOT of its argument.
Syntax
not boolean
not(boolean)Examples
not true == false not(false) == true
or
Returns the logical OR of its two arguments.
Syntax
boolean or boolean
or(boolean, boolean)Examples
false or true == true or(false, false) == false
smaller
Returns true of the first argument is smaller than the second.
Syntax
number < number
date < date
text < text
boolean < boolean
smaller(number, number)
smaller(date, date)
smaller(text, text)
smaller(boolean, boolean)Examples
10 < 8 == false
smallerEq
Returns true if the first argument is smaller than or equal to than the second.
Syntax
number <= number
date <= date
text <= text
boolean <= boolean
smallerEq(number, number)
smallerEq(date, date)
smallerEq(text, text)
smallerEq(boolean, boolean)Examples
10 <= 8 == false 8 <= 8 == true
unequal
Returns false if its arguments are equal, and true otherwise.
Syntax
value != value
unequal(value, value)Examples
(6 * 9 != 42) == false (true != not false) == false
Functions
String
concat
Concatenates its arguments and returns the result.
Syntax
concat(text...)Examples
"dog" +"go" == "doggo" concat("dog", "go") == "doggo"
contains
Returns true if the second argument is found in the first.
Syntax
contains(text, text)Examples
contains("notion", "ion") == true
format
Formats its argument as a string.
Syntax
format(value)Examples
format(42) == "42" format(true) == "true" format(now()) == "Wed Dec 31 1969
join
Inserts the first argument between the rest and returns their concatenation.
Syntax
join(text...)Examples
join("-", "a", "b", "c") == "a-b-c"
length
Returns the length of a string.
Syntax
length(text)Examples
length("Hello world") == 11
replace
Replaces the first match of a regular expression with a new value.
Syntax
replace(number, text, text)
replace(text, text, text)
replace(boolean, text, text)Examples
replace("1-2-3", "-", "!") == "1!2-3" replace("abcc abcccc", "abc{2,}", "!") == "! abcccc"
replaceAll
Replaces all matches of a regular expression with a new value.
Syntax
replaceAll(number, text, text)
replaceAll(text, text, text)
replaceAll(boolean, text, text)Examples
replaceAll("1-2-3", "-", "!") == "1!2!3" replaceAll("abcc abcccc", "abc{2,}", "!") == "! !"
slice
Extracts a substring from a string from the start index (inclusively) to the end index (optional and exclusively).
Syntax
slice(text, number)
slice(text, number, number)Examples
slice("Hello world", 1, 5) == "ello" slice("notion", 3) == "ion"
test
Tests if a string matches a regular expression.
Syntax
test(number, text)
test(text, text)
test(boolean, text)Examples
test("1-2-3", "-") == true
Date
date
Returns an integer number, between 1 and 31, corresponding to day of the month for the given.
Syntax
date(date)Examples
date(now()) == 13
dateAdd
Add to a date. The last argument, unit, can be one of: years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds.
Syntax
dateAdd(date, number, text)Examples
dateAdd(date, amount, "years") dateAdd(date, amount, "quarters") dateAdd(date, amount, "months") dateAdd(date, amount, "weeks") dateAdd(date, amount, "days") dateAdd(date, amount, "hours") dateAdd(date, amount, "minutes") dateAdd(date, amount, "seconds") dateAdd(date, amount, "milliseconds")
dateBetween
Returns the time between two dates. The last argument, unit, can be one of: years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds.
Syntax
dateBetween(date, date, text)Examples
dateBetween(date, date2, "years") dateBetween(date, date2, "quarters") dateBetween(date, date2, "months") dateBetween(date, date2, "weeks") dateBetween(date, date2, "days") dateBetween(date, date2, "hours") dateBetween(date, date2, "minutes") dateBetween(date, date2, "seconds") dateBetween(date, date2, "milliseconds")
dateSubtract
Subtract from a date. The last argument, unit, can be one of: years, quarters, months, weeks, days, hours, minutes, seconds, milliseconds.
Syntax
dateSubtract(date, number, text)Examples
dateSubtract(date, amount, "years") dateSubtract(date, amount, "quarters") dateSubtract(date, amount, "months") dateSubtract(date, amount, "weeks") dateSubtract(date, amount, "days") dateSubtract(date, amount, "hours") dateSubtract(date, amount, "minutes") dateSubtract(date, amount, "seconds") dateSubtract(date, amount, "milliseconds")
day
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.
Syntax
day(date)Examples
day(now()) == 3
end
Returns the end of a date range.
Syntax
end(date)Examples
end(prop("Date")) == Feb 2, 1996
formatDate
Format a date using the Moment standard time format string.
Syntax
formatDate(date, text)Examples
formatDate(now(), "MMMM D YYYY, HH:mm") == March 30 2010, 12:00 formatDate(now(), "YYYY/MM/DD, HH:mm") == 2010/03/30, 12:00 formatDate(now(), "MM/DD/YYYY, HH:mm") == 03/30/2010, 12:00 formatDate(now(), "HH:mm A") == 12:00 PM formatDate(now(), "M/D/YY") == 3/30/10
fromTimestamp
Returns a date constructed from a Unix millisecond timestamp, corresponding to the number of milliseconds since January 1, 1970.
Syntax
fromTimestamp(number)Examples
fromTimestamp(2000000000000) == Tue May 17 2033
hour
Returns an integer number, between 0 and 23, corresponding to hour for the given date.
Syntax
hour(date)Examples
hour(now()) == 17
minute
Returns an integer number, between 0 and 59, corresponding to minutes in the given date.
Syntax
minute(date)Examples
minute(now()) == 45
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.
Syntax
month(date)Examples
month(now()) == 11
now
Returns the current date and time.
Syntax
now()Examples
now() == Feb 2, 1996 6:30 PM
start
Returns the start of a date range.
Syntax
start(date)Examples
start(prop("Date")) == Feb 2, 1996
timestamp
Returns an integer number from a Unix millisecond timestamp, corresponding to the number of milliseconds since January 1, 1970.
Syntax
timestamp(date)Examples
timestamp(now()) == 1512593154718
year
Returns a number corresponding to the year of the given date.
Syntax
year(date)Examples
year(now()) == 1984
Math
abs
Returns the absolute value of a number.
Syntax
abs(number)Examples
abs(-3) == 3
cbrt
Returns the cube root of a number.
Syntax
cbrt(number)Examples
cbrt(8) == 2
ceil
Returns the smallest integer greater than or equal to a number.
Syntax
ceil(number)Examples
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.
Syntax
exp(number)Examples
exp(1) == 2.718281828459045
floor
Returns the largest integer less than or equal to a number.
Syntax
floor(number)Examples
floor(2.8) == 2
ln
Returns the natural logarithm of a number.
Syntax
ln(number)Examples
ln(e) == 1
log2
Returns the base 2 logarithm of a number.
Syntax
log2(number)Examples
log2(64) == 6
log10
Returns the base 10 logarithm of a number.
Syntax
log10(number)Examples
log10(1000) == 3
max
Returns the largest of zero or more numbers.
Syntax
max(number)Examples
max(5, 2, 9, 3) == 9
min
Returns the smallest of zero or more numbers.
Syntax
min(number)Examples
min(4, 1, 5, 3) == 1
round
Returns the value of a number rounded to the nearest integer.
Syntax
round(number)Examples
round(4.4) == 4 round(4.5) == 5
sign
Returns the sign of the x, indicating whether x is positive, negative or zero.
Syntax
sign(number)Examples
sign(4) == 1 sign(-9) == -1 sign(0) == 0
sqrt
Returns the positive square root of a number.
Syntax
sqrt(number)Examples
sqrt(144) == 12
toNumber
Parses a number from text. Using toNumber on a date will convert it milliseconds in Unix Epoch time.
Syntax
toNumber(text)
toNumber(number)
toNumber(boolean)
toNumber(date)Examples
toNumber("42") == 42 toNumber(false) == 0
Miscellaneous
empty
Tests if a value is empty.
Syntax
empty(number)
empty(text)
empty(boolean)
empty(date)Examples
empty("") == true
Moment.JS Date Formats
The Date functions use Moment.JS datetime formatting. Below is the reference to the supported formats.
Formatting Tokens
Year, month, and day tokens
Year, month, and day tokens
Tokens are case-sensitive.
Week year, week, and weekday tokens
Week year, week, and weekday tokens
For these, the lowercase tokens use the locale aware week start days, and the uppercase tokens use the ISO week date start days.
Tokens are case-sensitive.
Locale aware formats
Locale aware formats
Locale aware date and time formats are also available using LT LTS L LL LLL LLLL. They were added in version 2.2.1, except LTS which was added 2.8.4.
Tokens are case-sensitive.
Hour, minute, second, millisecond, and offset tokens
Hour, minute, second, millisecond, and offset tokens
Tokens are case-sensitive.
String Formats
ISO8601
An ISO 8601 string requires a date part.
2013-02-08 # A calendar date part
2013-W06-5 # A week date part
2013-039 # An ordinal date part
20130208 # Basic (short) full date
2013W065 # Basic (short) week, weekday
2013W06 # Basic (short) week only
2013050 # Basic (short) ordinal dateA time part can also be included, separated from the date part by a space or a uppercase T.
2013-02-08T09 # An hour time part separated by a T
2013-02-08 09 # An hour time part separated by a space
2013-02-08 09:30 # An hour and minute time part
2013-02-08 09:30:26 # An hour, minute, and second time part
2013-02-08 09:30:26.123 # An hour, minute, second, and millisecond time part
2013-02-08 24:00:00.000 # hour 24, minute, second, millisecond equal 0 means next day at midnight
20130208T080910,123 # Short date and time up to ms, separated by comma
20130208T080910.123 # Short date and time up to ms
20130208T080910 # Short date and time up to seconds
20130208T0809 # Short date and time up to minutes
20130208T08 # Short date and time, hours onlyAny of the date parts can have a time part.
2013-02-08 09 # A calendar date part and hour time part
2013-W06-5 09 # A week date part and hour time part
2013-039 09 # An ordinal date part and hour time partIf a time part is included, an offset from UTC can also be included as +-HH:mm, +-HHmm, +-HH or Z.
2013-02-08 09+07:00 # +-HH:mm
2013-02-08 09-0100 # +-HHmm
2013-02-08 09Z # Z
2013-02-08 09:30:26.123+07:00 # +-HH:mm
2013-02-08 09:30:26.123+07 # +-HHRFC 2822
The RFC 2822 date time format
Before parsing a RFC 2822 date time the string is cleansed to remove any comments and/or newline characters. The additional characters are legal in the format but add nothing to creating a valid moment instance.
After cleansing, the string is validated in the following space-separated sections, all using the English language:
6 Mar 17 21:22 UT
6 Mar 17 21:22:23 UT
6 Mar 2017 21:22:23 GMT
06 Mar 2017 21:22:23 Z
Mon 06 Mar 2017 21:22:23 z
Mon, 06 Mar 2017 21:22:23 +0000- Day of Week in three letters, followed by an optional comma. (optional)
- Day of Month (1 or 2 digit), followed by a three-letter month and 2 or 4 digit year
- Two-digit hours and minutes separated by a colon (:), followed optionally by another colon and seconds in 2-digits
- Timezone or offset in one of the following formats:
- UT : +0000
- GMT : +0000
- EST | CST | MST | PST | EDT | CDT | MDT | PDT : US time zones*
- A - I | K - Z : Military time zones*
- Time offset +/-9999
[*] See section 4.3 of the specification for details.
The parser also confirms that the day-of-week (when included) is consistent with the date.
Examples
Toggle Checkbox Based on Different Property
Formula
If Text Field is empty or Text Field is equal to Important then show a checked box, else show an unchecked one.
or(empty(prop("Text Field")), prop("Text Field") == "Important")
Formula
Thank you to David Thrale for this one!
if(dateBetween(prop("Target"), now(), "days") > 0, "✅", "❌")
Logic Examples
Basic If
if(true, "It's True", "It's False")
If Nesting
if(if(if(if(if(true, false, true), true, false), false, true), true, false), "It's True", "It's False")
If's will evaluate from inside out. For the above condition, it will go like so:
- FALSE:
if(true, false, true)
- FALSE:
if(if(true, false, true), true, false)
- TRUE:
if(if(if(true, false, true), true, false), false, true)
- TRUE:
if(if(if(if(true, false, true), true, false), false, true), true, false)
- "It's True":
if(if(if(if(if(true, false, true), true, false), false, true), true, false), "It's True", "It's False")
Example If to Count Number of Multi-Select Values
Formula
if(length(prop("People")) > 0, length(replaceAll(prop("People"), "[^,]", "")) + 1, 0)
You can also hide the People Count column and just use it as a behind the scenes calculation as well.
Assigning Point Values to Selectors
Method-Value
if(test(prop("Method"), "Fix"), 1, if(test(prop("Method"), "Test"), 2, if(test(prop("Method"), "Create"), 3, 0)))
Difficulty-Value
if(test(prop("Difficulty"), "Easy"), 1, if(test(prop("Difficulty"), "Medium"), 2, if(test(prop("Difficulty"), "Hard"), 3, 0)))
Point Value
prop("Method-Value") + prop("Difficulty-Value")
Method-Value and Difficulty-Value columns then so that it cleans up the display, you can see how to do that, as below under the table menu, ...
Date Examples
Return Number of Month
month(prop("Date")) + 1
The reason to add 1 is that this starts at 0 (January), this is then a more standard value.
Get Number of Years Between Dates
dateBetween(prop("Date"), prop("Origin Date"), "years")
Get Number of Days Between Dates
dateBetween(prop("Date"), prop("Origin Date"), "days")
Current Date & Time
now()
Is the Number of Days Between Two Dates over 21 Days
if(dateBetween(prop("Date"), prop("Now"), "days") > 21, "Yes", "No")
This is a more complex comparison that not only compares dates but also if it meets a certain criteria.
Get Number of Week
formatDate(now(), "w")
Weekday
formatDate(now(), "dddd")
Credit: Wayne Bishop
Midnight of Current Day
dateSubtract(dateSubtract(now(), hour(now()), "hours"), minute(now()), "minutes")
This can be useful if you need to compare dates prior to current day and additionally exclude today (now() will exclude just before current moment).
Created Time, Last Modified Time and formatting dates as Unix Epoch times will only go to minute precision.
Example: 1554728160000 this will not include second and fractional seconds.The above example was requested when very specifically formatted text was needed from a date calculation.
Formulas
Note: I am adding the +1 to include the end date in the calculation.
Days (inc. End Date): dateBetween(now(), prop("Born"), "days") + 1
Months: dateBetween(now(), prop("Born"), "months")
Years: dateBetween(now(), prop("Born"), "years")
Remaining Months: prop("Months") - 12 * prop("Years")
Remaining Days: dateBetween(now(), dateAdd(dateAdd(prop("Born"), prop("Years"), "years"), prop("Remaining Months"), "months"), "days") + 1
Formatted Text: concat(format(prop("Years")), " years, ", format(prop("Remaining Months")), " months, ", format(prop("Remaining Days")), " days")
Math Examples
Multiply by 8 if not 0
if(prop("First Number") > 0, prop("First Number") * 8, 0)
This is just an example of a simple numerical condition
Round Pi to n Decimal Places
0 Decimals: round(pi * 1) / 1
2 Decimals: round(pi * 100) / 100
3 Decimals: round(pi * 1000) / 1000
Keep in mind that using Floor can get the same result as rounding to 0 Decimals.
Note: Default round is a single decimal place.
Round Pi Up
ceil(pi)
Note: To the nearest whole number.
Round Pi Down
floor(pi)
Note: To the nearest whole number.
Subtraction
prop("Number 1") - prop("Number 2")
String Manipulations
Transform Text to Lower Case
replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(prop("Mixed Case String"), "A", "a"), "B", "b"), "C", "c"), "D", "d"), "E", "e"), "F", "f"), "G", "g"), "H", "h"), "I", "i"), "J", "j"), "K", "k"), "L", "l"), "M", "m"), "N", "n"), "O", "o"), "P", "p"), "Q", "q"), "R", "r"), "S", "s"), "T", "t"), "U", "u"), "V", "v"), "W", "w"), "X", "x"), "Y", "y"), "Z", "z")
Transform Lower Case Text to Upper Case
replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(prop("Lower Case String"), "a", "A"), "b", "B"), "c", "C"), "d", "D"), "e", "E"), "f", "F"), "g", "G"), "h", "H"), "i", "I"), "j", "J"), "k", "K"), "l", "L"), "m", "M"), "n", "N"), "o", "O"), "p", "P"), "q", "Q"), "r", "R"), "s", "S"), "t", "T"), "u", "U"), "v", "V"), "w", "W"), "x", "X"), "y", "Y"), "z", "Z")
Transform Lower Case Text to Capital Case
replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(replaceAll(prop("Lower Case String"), "^a", "A"), "^b", "B"), "^c", "C"), "^d", "D"), "^e", "E"), "^f", "F"), "^g", "G"), "^h", "H"), "^i", "I"), "^j", "J"), "^k", "K"), "^l", "L"), "^m", "M"), "^n", "N"), "^o", "O"), "^p", "P"), "^q", "Q"), "^r", "R"), "^s", "S"), "^t", "T"), "^u", "U"), "^v", "V"), "^w", "W"), "^x", "X"), "^y", "Y"), "^z", "Z"), " a", " A"), " b", " B"), " c", " C"), " d", " D"), " e", " E"), " f", " F"), " g", " G"), " h", " H"), " i", " I"), " j", " J"), " k", " K"), " l", " L"), " m", " M"), " n", " N"), " o", " O"), " p", " P"), " q", " Q"), " r", " R"), " s", " S"), " t", " T"), " u", " U"), " v", " V"), " w", " W"), " x", " X"), " y", " Y"), " z", " Z")
Source: https://notion.vip/simulate-lower-upper-and-proper-functions-in-notion-databases/
Count & Contains Examples
Count Number of People in Column
if(length(prop("People")) > 0, length(replaceAll(prop("People"), "[^,]", "")) + 1, 0)
Note: Count number of comma's in the People column.
Count Number of Checkboxes Checked
toNumber(prop("Check 1")) + toNumber(prop("Check 2")) + toNumber(prop("Check 3"))
Note: Downside is you have to manually specify each Column.
Test if Tags Field Contains Values
if(or(contains(prop("Tags"), "MyTag"), contains(prop("Tags"), "AnotherTag")), "Contains Tags", "Doesn't Contain Tags")
Grade Example
Weighted Grades
prop("Grade") * prop("Weighting")
Traditionally weighted grades are a percentage of the total which you then add up. In this case I used the Sum total at the end.
Missing Grades
(prop("Grade") > 0) ? false : true
Another idea for this is to have a related table that does the average, sum's and number of missing assignments as the total's are not usable via a formula in another page.
Source: Silvan