You can calculate the number of days between the Start Date and End Date in the ‘Date’ property on Notion using the dateBetween() function.
Notion is a workspace application that provides you the ability to take notes, manage tasks and projects, manage databases, create schedules, collaborate, and much more. It is a collaborative platform that blends everyday work components such as notes, writing tools, to-do lists, kanban, wikis, spreadsheets, databases, calendars, and reminders into one. Notion is supported by various platforms including Android, iOS, Mac, Windows, and Web.
It is a great tool for creating and managing to-do lists, calendars, timelines, schedules and, reminders. When working with calendars and reminders, we need to manipulate dates a lot. So sometimes you need to calculate how many days are between specific start and end dates. That is the goal of this article, to teach you how to calculate days between start and end date on Notion.
How to Calculate Days Between Two Dates on Notion
One of the most useful database components you can create and manage in Notion is a table, it can be inline tables or full-page tables. In Notion, each column of a table is called Property. Formula property calculates results based on other properties (variables).
In Notion, formulas are column-based which means you can only apply formulas to an entire column. And you can’t apply a formula to a single row in a table.
In a database, for various purposes, you would want to calculate the number of days between two dates. For example, to calculate how many days are between the start and the end date of each patient’s treatment.
To calculate days between two dates you need to use dateBetween() function, which not only calculates days, but weeks, quarters, months, years, hours, minutes, seconds, and milliseconds between the start and end dates. This function only works on the date property (variable).
Let’s say you have the following table in Notion about a project. And you need to count the number of days between the start and end dates of each task.
Now we are going to use the
dateBetween() function to calculate the days between the Start Date and the End Date. For this to work, the Date column (Schedule) in your table should be in Date property type.
To change the property type to ‘Date’, click on the column title and select the ‘Date’ type under the ‘Property type’ section as shown in the image below.
You don’t need to create two separate columns to add the start and end date into the Notion table.
You can just enter Start and End within the same column using the date selector calendar menu. To do that, click on a cell in the date column (property) and you will get a calendar window where you can select a date.
First, select the start date in the calendar window. Then, enable the ‘End date’ toggle switch.
Then, select the end date and you’ll see all the dates in between are marked with blue color.
To calculate days between, add a new column and change its name to ‘Days in Between’. When a new property (column) is added to the table, its default property type would be ‘Text’, so change its property type to ‘Formula’ from the Property Type menu (as shown below).
The Syntax of dateBetween():
dateBetween(date, date, text)
Where date argument specifies the name of the date property which contains both ‘Start Date’ and ‘End Date’. And Text arguments should be one of the units like days, weeks, quarters, months, years, hours, minutes, seconds, and milliseconds.
In the table, click on any cell in your formula column. This would bring up the formula window (as shown below), where you can type your formula.
In the formula window, on the left-hand panel, you can scroll and find several sections such as Properties, Constants, Functions, Operators, etc., When you hover over an item on the left, you can find its description, syntax, and examples on how to use it.
Type the below formula in the text box at the top of the window.
dateBetween(end(prop("Schedule")), start(prop("Schedule")), "days")
Note: In the above formula,
start(prop("Schedule")) refer to the column that contains both Start Date and End Date, while “days” in the last argument specify days unit. You must replace
Schedule with the property name of the column with Start and End Date in your Notion table.
Once you start typing your formula, you can see the related commands below which you could use in your formula. When you click on it, it will be added to your formula and save you the trouble of manually typing them.
As you finish typing the formula the result would immediately appear on the column before you even click ‘Done’.
Click ‘Done’ or press
Ctrl + Enter or click anywhere outside the formula window to close it.
Now, you got the number of days between the Start Date and End Date of each task in the table. As you can see below, if you have a Start and an End Date that are the same (for Task 5), you would get a ‘0’.
Now, you know how to calculate days between two dates on Notion.