Understanding the relative week number

I created this formula to use in an Airtable content calendar but it has a range of useful applications.

The “Relative week” formula takes any date as its input and returns a number that corresponds to the week, relative to the current week, where -1 is last week, 0 is this week, 1 is next week, 2 is the week after, and so on, in both directions. Monday is used as the start of the week, and for every day in the same week, the formula returns the same value. I’ve found this formula to be really useful for a couple specific applications, including making content calendars. This post includes a solution for spreadsheets, Airtable, Salesforce, and Notion.

As an illustration, here’s a calendar with dates and the corresponding “relative week” number (i.e. what the formula would return when that date is used as an input), where the current day is Wednesday, 3/10. Notice that for every date in the current week, the formula returns 0, and for every date in the following week, the formula returns 1.

On the next day, 3/11, the formula outputs would be the same. Same for 3/12, 3/13, and 3/14, because they all fall within the same week.

Once 3/15 rolls around, bumping us into the next week, the formula outputs would shift:

Why would you use this formula?

I typically use this formula for two purposes.

1 – It allows me to create views filtered to a set of weeks relative to the current week. For example, to create a view which includes all records with dates during the prior week, the current week, and the next week. With the built-in relative date filters, this isn’t possible.

2 – It allows me to label a date with a natural language description of the relative week, e.g. “this week”, “next week”, “later”, and other labels.

You can use the formula in combination with the calendar week date format to create labels that look like this:

2021-W27 - last week
2021-W28 - this week
2021-W29 - next week
2021-W30 - later
2021-W31 - later

This is a great, intuitive, user-friendly way to organize a content calendar in Airtable using a grouped list view.

Relative week number formulas

Below you’ll find relative week number formulas for spreadsheets, Airtable, Salesforce and Notion.

Spreadsheet relative week number formula

Replace A2 (in three places) with the cell reference for the date you’d use as an input.

💡

=IF(ISDATE(A2),CEILING((A2-WEEKDAY(A2, 2)+1-TODAY())/7),"")

Airtable relative week number formula

Replace {Date} (in three places) with the name of the date column that you’re using.

💡

IF( {Date}=BLANK(), BLANK(), CEILING( (1/7) * DATETIME_DIFF( DATEADD({Date}, -WEEKDAY({Date}, "Monday"), "days"), TODAY(), "days" ) ) )

Salesforce relative week number formula

Replace Date (in two places) with the name of the date field that you’re using.

💡

MCEILING((Date - MOD(WEEKDAY(Date) + 5, 7) + 1 - TODAY())/7)

Notion relative week number formula

Replace prop("Date") (in three places) with the string for the date field that you’re using.

💡

if(prop("Date") == fromTimestamp(toNumber("")), toNumber(""), ceil((dateBetween(prop("Date"), now(), "days") - if(day(prop("Date")) == 0, 7, day(prop("Date")) - 1) - 1) / 7))

Email signup

Relative week formula