By Alex Sherwood aka aNotioneer

<aside> 👋 By default Notion's dateBetween function takes the times of your dates into consideration, as well as the days, when it calculates the number of days between two dates.

So if you have a task that's due at 9am tomorrow and it's past 9am today, dateBetween will show 0. If you haven't set a time for a date then Notion assumes the the time is 00:00 aka midnight. That means that dateBetween will show 0 for any date that's tomorrow.

This formula uses formatDate to get the day number, rather than dateBetween to calculate the number of calendar days between two dates, ignoring times.

Feel free to add a comment if you have any questions 😄

</aside>

Examples

• 📸 Examples screenshot - this is how the table looked on the date when this template was created

Get the day number from a date

This works out how many days into the year a date is e.g. 1st February = 32.

formatDate(prop("Due Date"), "DDD")

Get number of calendar days between two dates

Notion doesn't recognise the output of the formatDate function as a number so I've used toNumber to turn the output into a number.

toNumber(formatDate(prop("Due Date"), "DDD")) - toNumber(formatDate(now(), "DDD"))

Adjust the calculation if the date is next year

if(year(prop("Due Date")) > year(now()), toNumber(formatDate(prop("Due Date"), "DDD")) + 365 - toNumber(formatDate(now(), "DDD")), toNumber(formatDate(prop("Due Date"), "DDD")) - toNumber(formatDate(now(), "DDD")) )

Adjust the calculation if the date is last year

if(year(prop("Due Date")) < year(now()), toNumber(formatDate(prop("Due Date"), "DDD")) - 365 - toNumber(formatDate(now(), "DDD")), toNumber(formatDate(prop("Due Date"), "DDD")) - toNumber(formatDate(now(), "DDD")) )

🎉 Finished Formula

This formula combines the 3 formulas from earlier in the guide.