Being able to see a date relative to today can be super useful, not just to visualize how much time is left for a task, but also to use with filters to narrow down certain date ranges (for example, only show overdue tasks for “Earlier This Week”). Notion’s date property has a limited relative date format built in, however, writing a custom formula give us the ability to create unlimited relative date displays that will meet any of our unique needs.
Here I’ve built a formula that is based on calendar weeks and months, and displays the following relative dates:
Past:
- This Week (Yesterday)
- Earlier This Week
- Last Week
- Earlier This Month
- Last Month
- Earlier This Year
- Last Year
- Before Last Year
Present:
- This Week (Today)
Future:
- This Week (Tomorrow)
- This Week
- Next Week
- This Month
- Next Month
- This Year
- Next Year
- Beyond Next Year
This assumes that you have a property in your database called “Date”.
Pro tip: if you want your date property to be called something else, like “Due Date”, name it “Date” first, apply the formula to the “Relative Date” property, then change the “Date” property to whatever name you would like. The formula will automatically update all references to the new property name.
Here is the formula. You may use as is, or use as a starting point to modify and build upon. Cheers!
if(year(prop("Date")) < toNumber(year(now())), if(year(prop("Date")) == toNumber(year(now())) - 1, "Last Year", if(year(prop("Date")) < toNumber(year(now())) - 1, "Before Last Year", "?")), if(year(prop("Date")) > toNumber(year(now())), if(year(prop("Date")) == toNumber(year(now())) + 1, "Next Year", if(year(prop("Date")) > toNumber(year(now())) + 1, "Beyond Next Year", "?")), if(year(prop("Date")) == toNumber(year(now())), if(toNumber(formatDate(prop("Date"), "w")) == toNumber(formatDate(now(), "w")), if(toNumber(formatDate(prop("Date"), "D")) == toNumber(formatDate(now(), "D")), "This Week (Today)", if(toNumber(formatDate(prop("Date"), "D")) == toNumber(formatDate(now(), "D")) + 1, "This Week (Tomorrow)", if(toNumber(formatDate(prop("Date"), "D")) == toNumber(formatDate(now(), "D")) - 1, "This Week (Yesterday)", if(toNumber(formatDate(prop("Date"), "D")) < toNumber(formatDate(now(), "D")), "Earlier This Week", "This Week")))), if(toNumber(formatDate(prop("Date"), "w")) == toNumber(formatDate(now(), "w")) + 1, "Next Week", if(toNumber(formatDate(prop("Date"), "w")) == toNumber(formatDate(now(), "w")) - 1, "Last Week", if(toNumber(formatDate(prop("Date"), "M")) == toNumber(formatDate(now(), "M")), if(toNumber(formatDate(prop("Date"), "D")) < toNumber(formatDate(now(), "D")), "Earlier This Month", "This Month"), if(toNumber(formatDate(prop("Date"), "M")) == toNumber(formatDate(now(), "M")) + 1, "Next Month", if(toNumber(formatDate(prop("Date"), "M")) == toNumber(formatDate(now(), "M")) - 1, "Last Month", if(toNumber(formatDate(prop("Date"), "w")) < toNumber(formatDate(now(), "w")), "Earlier This Year", "This Year"))))))), "?")))
