🧰 Notion Formulas
Learn how to use Notion’s dateBetween formula function to calculate between two dates in your database table. This function is very useful for task managers, deadline alerts, timesheets, and more. I’ll show you how to calculate between two dates in your table, between a single date and now, and between dates in a date range. In addition, I’ll describe how the syntax for dateBetween changes for time properties. Here’s how it works.
Learning Hub
Track learning with spaced repetition and smart formulas in this easy-to-use hub.
What You Need To Use dateBetween
To use the dateBetween function in Notion, you’ll first need to create a table database in any page via the block menu (trigger: /table). The three following property combinations can be used inside the database:
Start Date
End Date
Formula
Date Range
Formula
Date
Formula
What To Calculate With The dateBetween Function
You can calculate the following between two dates with the dateBetween function:
*note: see these examples in action via notion page below article.
Years
Quarters
Months
Weeks
Days
Hours
Minutes
How To Calculate between Dates Without Time In Notion
There are two formula syntaxes to calculate between dates inside Notion. One is for calculating between dates with time, and the other is for dates without time. Here is how to calculate without time.
Calculate Two Separate Dates
The formula to calculate between separate date properties requires you to locate the end date first, and the start date second. You will then define what date variable to calculate between the properties (see above).
copy + paste
dateBetween(prop("Name of end date property"), prop("Name of start date property"), "variable")
Calculate Date Range
The formula to calculate between dates in a date range again requires you to locate the end date first, and the start date second. To do this, nest your date property inside the end() and start() functions. You will then define what date variable to calculate between the properties.
copy + paste
dateBetween(end(prop("Name of date range property")), start(prop("Name of date range property")), "variable")
How To Calculate Between Dates With Time In Notion
Let’s now look at how the syntax changes for the dateBetween function in Notion when dates have a time defined. You can still use the syntax above, however, the result will not be the same.
note: if you have a table with a combination of dates with and without time, the formula below is ideal.
Calculate Two Separate Dates
If there is a start and end date that are 1 day apart, but technically 23 hours apart and not quite 1 day, like above, the days between will return 0. To make this return 1 instead, you will need to convert both dates to 12:00am, before using the dateBetween function.
The syntax to convert a date to 12:00 am looks like this:
copy + paste
dateBetween(dateSubtract(dateSubtract(prop("Name of date property"), hour(prop("Name of date property")), "hours"), minute(prop("Name of date property")), "minutes")
With the syntax above to convert dates to 12:00am, we can convert both the end and start dates to 12:00am, then plug them into the dateBetween function. Again, the end date is located before the start. Define the variable at the end like this:
copy + paste
dateBetween(dateSubtract(dateSubtract(prop("Name of end date property"), hour(prop("Name of end date property")), "hours"), minute(prop("Name of end date property")), "minutes"), dateSubtract(dateSubtract(prop("Name of start date property"), hour(prop("Name of start date property")), "hours"), minute(prop("Name of start date property")), "minutes"), "variable")
Calculate Now and Date
Here is how to calculate between hybrid dates: one with a time, and one without.
The formula to calculate between the date of now and a single date requires you to locate the date first, and date of now second. To tell the formula to find the date of today, use the now() function. You will then define what date variable to calculate between the properties.
The now() function is a timestamp. A timestamp is defined by date and time, so we’ll have to convert now() to 12:00am like above. In this example, the deadline does not have a time and can be left as is.
copy + paste
dateBetween(prop("Name of date property"), dateSubtract(dateSubtract(now(), hour(now()), "hours"), minute(now()), "minutes"), "variable")