Viewing records in the current week

Show and Tell
1

Suppose you want to view all the records for the current week, based on a {Due Date} field. The {Due Date} could be any day of the week: Monday through Friday, and you want to see all the records within the current week, whether or not they are before or after the current day. For example, on Wednesday, you want to see records that were due back on Monday as well as records that won’t come due until Friday.

Airtable filters work well for showing dates before or after a given date, or within a specific interval of dates before or after the current date. However, Airtable filters don’t do well when the number of dates before/after the current date change.

One answer is to create a formula field that converts both the {Due Date} and TODAY() to the starting day of the week and compares them. You can then fitter on the result. In the screen shot below, there are two comparison fields, one that just checks if the record is due this week, another that gives a number indicating if the record is in the current week, next week, previous week, etc.

image
image894×636 27 KB

Here is a formula that converts a date to the preceding Sunday.

DATEADD(
    {Due Date},
    WEEKDAY({Due Date}) * -1,
    "days"
)

The other formulas are easily derived from this one.

  • To find the preceding Sunday for the current week, use TODAY() instead of {Due Date}
  • To compare the two calculated dates, use DATETIME_DIFF() or IS_SAME().
4 Likes
2

Love it! Great tip! :star_struck: :clap:

3

Thanks so much for sharing!

JFYI for others: In attempting to do this without time zone and then using New_York time zone, it kept outputting Saturday as the start of the week. I fixed it by going into the Formula Formatting and using the GMT/UTC time zone for all collaborators.

4

Is you Date field a date-only field or does it include time as well? If your field includes a time as well as a date, I recommend using SET_TIMEZONE() in the formula instead of using GMT/UTC timezone for all collaborators. Using the GMT/UTC timezone for all collaborators but actually putting in the local time can cause downstream problems.

WEEKDAY(SET_TIMEZONE({Due Date/Time}, 'America/New_York'))

You may also have to adjust the formula to parse the date without the time.

DATEADD(
    DATETIME_PARSE(
        DATESTR(SET_TIMEZONE({Due Date/Time}, "America/New_York")),
        "YYYY-MM-DD"
    ),
    WEEKDAY(SET_TIMEZONE({Due Date/Time}, "America/New_York")) * -1,
    "days"
)
1 Like
5

No time, only dates weirdly enough. Not sure why mine is defaulting to Saturday, but I’ll do the set_timezone workaround to avoid issues.

UPDATE: adding timezone worked. Now the dates are Sundays as desired. Thanks!