Convert Dates in Notion into Calendar Weeks using Relative Time Frames e.g. "next week" | guide

Alex Sherwood
Aug 31 '19 · 6 min read · 3006 views
👋
This guide shows you how to translate a date e.g. a due date, into relative timeframes based on calendar weeks like "last week", "this week", "next week". I find this is really useful for quickly getting a sense of how long I have left to complete a task, as we generally review our tasks on a weekly basis in my company. The date format in this guide considers Sunday as the start of the week. To use Monday as the start of the week instead, change the lowercase "w" to an uppercase "W". Let me know if you have any questions / feedback here.

📸 Examples

  • Example table

👨‍🏫 Guide

🚨
If you get an error message about a ‘syntax error’ when copying and pasting these formulas, try copying the ‘Unformatted formula’ from the toggle list instead.

Managing dates in different years

Your formula needs to check for dates that're in different years before it takes care of dates in the same year.

If the date is last year

  • Unformatted formula
    if(year(prop("Due")) < year(now()), "Overdue","")
if(year(prop("Due")) < year(now()), "Overdue","")

If the date is next year

This formula does not allow for dates that're 2+ years away but it can easily be adapted to do so, if you need it to.
  • Explanation
    This is a 'nested' if( formula. If the first if( (in bold) evaluates to true i.e. if the year of the Due property's date if greater than the year now(), then it will check to see how many calendar weeks away the Due date is. If not, it will simply evaluate to false and you can ignore all of the other if( statements.
    Notion treats week 53 of a year as week 1 of the next year so 31st December 2019 translates to week 1 in a Notion formula, as well as 1st January 2020, which is why I've not added 52 to the first nested if( statement in the below formula.
  • Unformatted formula
    if(year(prop("Due")) > year(now()),
    if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) >= 5, "Later", ""))))))
    ,"")
if(year(prop("Due")) > year(now()), if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) >= 5, "Later", "")))))) ,"")
  • Testing
    You can test this formula by replacing the year(now()) with the current year and the toNumber(formatDate(now() with 52 e.g.
    if(year(prop("Due")) > 2019, if(toNumber(formatDate(prop("Due"), "w")) + 52 - 52 == 1, "Next week",""), "")
    • long test formula
      if(year(prop("Due")) > 2019, if(toNumber(formatDate(prop("Due"), "w")) + 52 - 52 == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - 52 == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - 52 == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - 52 == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - 52 >= 5, "Later", ""))))) ,"")

Managing dates in the same year

If the date is in a previous calendar week

  • Unformatted formula
    if(toNumber(formatDate(prop("Due"), "w")) < toNumber(formatDate(now(), "w")), "Overdue","")
if(toNumber(formatDate(prop("Due"), "w")) < toNumber(formatDate(now(), "w")), "Overdue","")

If the date is in this calendar week

  • Unformatted formula
    if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week","")
if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week","")

If the date is in the future (in 1 week)

The finished formula returns different results for dates 1 - 4 weeks away.
  • Unformatted formula
    if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 1, "Next week","")
if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 1, "Next week","")

If the date is a long way off

  • Unformatted formula
    if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) >= 5, "Later","")
if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) >= 5, "Later","")

✨ Finished formula

  • Unformatted formula
    if(year(prop("Due")) < year(now()), "Overdue", if(year(prop("Due")) > year(now()), if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) >= 5, "Later", ""))))))
    ,
    if(toNumber(formatDate(prop("Due"), "w")) < toNumber(formatDate(now(), "w")), "Overdue", if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) >= 5, "Later", "")))))))))
if(year(prop("Due")) < year(now()), "Overdue", if(year(prop("Due")) > year(now()), if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) >= 5, "Later", "")))))) , if(toNumber(formatDate(prop("Due"), "w")) < toNumber(formatDate(now(), "w")), "Overdue", if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) >= 5, "Later", "")))))))))
  • Alternative formula which returns "Overdue" if the date is before today(), even if it's in the same calendar week.
    • Unformatted formula
      if(year(prop("Due")) > year(now()), if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) >= 5, "Later", ""))))))
      ,
      if(dateBetween(prop("Due"), now(), "days") < 0, "Overdue", if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) >= 5, "Later", ""))))))))
    if(year(prop("Due")) > year(now()), if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) + 52 - toNumber(formatDate(now(), "w")) >= 5, "Later", "")))))) , if(dateBetween(prop("Due"), now(), "days") < 0, "Overdue", if(toNumber(formatDate(prop("Due"), "w")) == toNumber(formatDate(now(), "w")), "This week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 1, "Next week", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 2, "2 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 3, "3 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) == 4, "4 weeks", if(toNumber(formatDate(prop("Due"), "w")) - toNumber(formatDate(now(), "w")) >= 5, "Later", ""))))))))

🙇‍♂️ Acknowledgements

Thanks to Liel Villa for helping me with the conversion of the dates into numbers, to enable calculations with them.
Thanks to Bob Bellchambers-Wilson for pointing out that the formula also needs to handle dates in different years and that some people may want the formula to return "Overdue" if a date is in the past but still in the same calendar week.

If you're still learning to use Notion, I'd highly recommend checking out Francesco D'Alessio's courses -
(this is an affiliate link, I'll receive 30% of fees that you pay for if you sign up after using this link)
Updating...

Share on