You want to calculate the number of days between two dates inside a Notion database. Notion formulas use a built-in function called dateBetween to return the difference in days, hours, minutes, or seconds. This article explains the correct syntax for the dateBetween function, shows how to handle empty or future dates, and covers common mistakes that produce wrong results or error messages.
Key Takeaways: Notion Date Formula for Day Difference
- dateBetween(End, Start, ‘days’): Returns the number of days between two date properties; the order of arguments determines a positive or negative result.
- prop(“Date 2”) – prop(“Date 1”): A shorthand subtraction that also works if both properties are date type, but it returns a duration in milliseconds by default.
- if(empty(prop(“Date 2”)), “”, dateBetween(…)): Use this pattern to avoid blank or error outputs when one of the date fields is empty.
How the dateBetween Function Works in Notion
Notion formulas are written in a property of type Formula. The dateBetween function takes three arguments: the later date, the earlier date, and the unit of time. The unit must be a string inside single quotes, for example 'days', 'hours', 'minutes', 'seconds', 'weeks', 'months', or 'years'. If the first date is earlier than the second date, the result is a negative number. To always get a positive number, you can wrap the function with abs().
The formula property does not require you to install any add-on or integration. It works with any standard date property, including dates from a database relation or a rollup. The only prerequisite is that both inputs are valid Notion date values. If one of the properties is empty, the formula returns an error or a blank output depending on how you write the condition.
Syntax of dateBetween
The exact syntax is:
dateBetween(prop("End Date"), prop("Start Date"), "days")
Replace End Date and Start Date with the actual names of your date columns. The unit string must be lowercase and inside double quotes. Notion does not accept plural forms like 'day' or 'dayes'.
Steps to Add a Days-Between Formula to Your Database
- Open the database where you want the formula
Click the database name at the top of the page in Notion. Make sure the database already has at least two date columns, for exampleStart DateandEnd Date. - Add a new Formula property
Click the+button in the last column header. From the property type menu, select Formula. Name the propertyDays Betweenor any label you prefer. - Write the basic dateBetween formula
In the formula editor, type:dateBetween(prop("End Date"), prop("Start Date"), "days")
Click Done. The column now shows the number of days between the two dates. If the end date is before the start date, the number is negative. - Make the result always positive
If you want the absolute difference regardless of order, wrap the function withabs():abs(dateBetween(prop("End Date"), prop("Start Date"), "days"))
Click Done. Now the result is always a non-negative number. - Handle empty date fields
If either date property is empty, the formula shows an error. To show a blank cell instead, use anifcondition:if(empty(prop("End Date")) or empty(prop("Start Date")), "", dateBetween(prop("End Date"), prop("Start Date"), "days"))
Click Done. Empty rows now display a blank cell instead of an error.
Common Mistakes and How to Avoid Them
Formula returns an error when a date is empty
This happens because dateBetween requires two valid date values. Use the if(empty(...)) pattern shown in step 5 to return an empty string. You can also return 0 or a custom message like "No end date" instead of an empty string.
Result shows a large number like 86400000
This occurs when you subtract two date properties directly without using dateBetween. For example prop("End Date") - prop("Start Date") returns the difference in milliseconds, not in days. Always use dateBetween with the 'days' unit to get the correct number.
Negative numbers appear when dates are swapped
If the first argument is earlier than the second, the result is negative. Wrap the entire function with abs() to get the absolute value. Alternatively, swap the order of the arguments if you always want a positive number.
Formula does not update when a date changes
Notion formulas recalculate automatically whenever any referenced property is edited. If the formula seems stuck, refresh the page by pressing F5 or close and reopen the database view. If the problem persists, check that the formula property is not accidentally set to a static value.
Notion Date Formula Methods Compared
| Method | dateBetween Function | Direct Subtraction |
|---|---|---|
| Description | Explicit function with unit argument | Minus operator between two date properties |
| Result unit | Days, hours, minutes, etc | Milliseconds by default |
| Handles empty dates | Requires if(empty()) wrapper | Returns error with empty dates |
| Absolute value support | Wrap with abs() | Wrap with abs() and divide by 86400000 |
| Readability | Clear and self-documenting | Harder to read and maintain |
| Best use case | All day-difference calculations | Not recommended for day calculations |
For most users, the dateBetween function is the correct choice. Direct subtraction only makes sense if you need the raw millisecond value for a custom calculation.
You can now calculate the exact number of days between any two dates in your Notion database using the dateBetween function. Add the if(empty()) wrapper to prevent errors on incomplete rows. For more advanced use, combine dateBetween with formatDate() to display the result as a string like “3 days overdue”.