Red Gregory

View Original

Essentials Guide To Notion's dateBetween Function

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:

  1. Start Date

  2. End Date

  3. Formula

  1. Date Range

  2. Formula

  1. Date

  2. 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")

See this content in the original post

Further Reading

See this gallery in the original post