Notion's Essential Date Functions

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)
• Number
• Boolean (true or false)
• Date

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 string to a number.
• You can't concatenate a date with a string.

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.

• subtract(number, number)
• 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:

1. Reference a Date property.
2. The now() function.
3. The fromTimestamp() function.

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

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

start() and end()

Within a 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 start() and 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:

end(prop("Dates"))

dateBetween()

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:

1. Date — The date from which to subtract Argument 2.
2. Date — The date to subtract from Argument 1.
3. String — The unit for the returned value, such as "years", "months" or "days" — always lowercase and plural.

Our Age example subtracts prop("Birthday") from now() and displays the difference in "years":

dateBetween(now(), prop("Birthday"), "years")

For Days Remaining, we subtract now() from prop("Deadline") and display the difference in "days":

dateBetween(prop("Deadline"), now(), "days")

The 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"
)

formatDate()

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().

The formatDate() function takes two arguments:

1. Date — The date to reformat, which is typically a property reference.
2. 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.

dateAdd() and dateSubtract()

With the dateAdd() and dateSubtract() functions, you can add or subtract time from a date to return a new date. Each takes three arguments:

1. Date — The starting date, typically a reference to a Date property.
2. Number — The amount to add or subtract, which can be a literal value or a reference to another property.
3. String — The units for Argument 2, such as "years" or "days".

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.