Prior to this lesson, you’ll want a sound understanding of formula fundamentals. For all lessons in a natural sequence, along with videos, functional demos, practical exercises and certification questions, join Notion A-to-Z.
Dates are useful in any database, from task deadlines, to meeting times, to content publishing dates, to birthdays. With formulas, you can reference dates to calculate new dates and other insights. For example, you can automatically determine:
- days remaining until a task’s deadline;
- the duration of an event;
- a person’s age; or
- a project’s timeline, based on its first and last tasks.
Formulas also allow you to format dates beyond Notion’s limited native formatting options.
In this lesson, you’ll learn the essential functions for working with dates in formulas, some of which reiterate concepts from other lessons for your practice and deepest comprehension.
The Date Data Type
Remember, each value of a database property, including those returned by formulas, is one of four data types:
- String (text)
- Boolean (
When you provide input values to formulas, whether literally or as property references, you must remain mindful of data types.
- You can’t add a
- You can’t concatenate a
These will throw a “type mismatch,” which is the most common error you’ll encounter.
Moreover, the arguments of most functions must be a particular data type, or at least matching data types.
join(string, string, string)
dateSubtract(date, number, string)
Unlike the other data types,
dates cannot be entered literally. You have three ways to include a
date in a formula, which we’ll explore in this lesson:
- Reference a
Among other reasons, this makes data type-awareness particularly important when working with dates. We’ll explore the first two methods in this lesson. The third,
fromTimestamp(), is used infrequently; I touch on it in Formula Fundamentals.
Essential Date Functions
now() function returns the current date and time whenever the page is loaded. It accepts no arguments.
As you’ll see
now() is often used as an input within larger formulas.
Date property, you have the option to include an
End date, which forms a “date range.” Among myriad examples, this is useful for displaying project phases and multi-day events.
Sometimes you’ll find it helpful to extract the start or end date of a range. That’s the purpose of the
end() functions, both of which accept a single argument: the property containing the date range.
In a database of tasks, where a Dates property contains the completion period, a Deadline property can use
end() to extract the final day:
Among the most common uses of dates in formulas is to calculate the amount of time since or until a date. For example:
- an Age property can display the amount of time, in years, since the person’s birthday; and
- a Days Remaining property can display the number of days until the deadline.
These calculations use the
dateBetween() function, which accepts three arguments:
Date— The date from which to subtract Argument 2.
Date— The date to subtract from Argument 1.
String— The unit for the returned value, such as
"days"— always lowercase and plural.
Our Age example subtracts
now() and displays the difference in
dateBetween(now(), prop("Birthday"), "years")
For Days Remaining, we subtract
prop("Deadline") and display the difference in
dateBetween(prop("Deadline"), now(), "days")
dateBetween() function is most often used with
now(), but it accepts any two dates. Therefore, you can subtract a range’s
start() from its
end() to calculate its duration:
dateBetween( end(prop("Dates")), start(prop("Dates")), "days" )
When populating a
Date property, you can choose from just a handful of formatting options. Within a
Formula property, however, you can display that date in virtually any format by applying it to
formatDate() function takes two arguments:
Date— The date to reformat, which is typically a property reference.
String— The format in which to display the date, following the Moment.js standard.
"YYYY-MM-DD", for example, is unsupported natively in Notion. For details of Moment.js formatting, reference the Devhints cheatsheet.
dateSubtract() functions, you can add or subtract time from a
date to return a new
date. Each takes three arguments:
Date— The starting date, typically a reference to a
Number— The amount to add or subtract, which can be a literal value or a reference to another property.
String— The units for Argument 2, such as
In the example below, the Return property adds Duration (Days) to Departure.
dateAdd( prop("Departure"), prop("Duration (Days)"), "days" )
If you hit any snags as you tinker with date functions, feel free to tweet @WilliamNutt.