Notion Formulas

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

Tokens are case-sensitive.

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

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 date

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

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

If 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     # +-HH

RFC 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