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

Aug 31 '19 · 6 min read · 3006 views

# 📸 Examples

• Example table

# 👨‍🏫 Guide

## 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 -