Calculate Date of Previous Week's Friday

[Nicole_Flewelle]

I’ve tried cobbling together a fix from many other related responses, but nothing’s worked, so here’s my conundrum. I have to schedule social media posts, but I designate one day a week (Friday), to draft/schedule all the posts that will be going live the following week.

How can I create a formula to calculate the date of the previous week’s Friday (i.e., if Go-Live date is Thurs 12/27/18, then the Schedule Post date should calculate as Friday 12/21/18)?

[Jeremy_Oglesby]

This should work for you, Nicole:

SWITCH(
  WEEKDAY({Go-Live Date}),
  1, DATEADD({Go-Live Date}, -3, 'days'),
  2, DATEADD({Go-Live Date}, -4, 'days'),
  3, DATEADD({Go-Live Date}, -5, 'days'),
  4, DATEADD({Go-Live Date}, -6, 'days'),
  5, DATEADD({Go-Live Date}, -7, 'days'),
  6, DATEADD({Go-Live Date}, -1, 'days'),
  0, DATEADD({Go-Live Date}, -2, 'days')
)

The SWITCH() statement says "evaluate the first argument I pass you <<WEEKDAY({Go-Live Date})>>, and if it returns x, then return y in this field.

The WEEKDAY({Go-Live Date}) returns a number indicating what day of the week that date represents (0=Sunday, 1=Monday, 2=Tuesday, etc.).

So when WEEKDAY({Go-Live Date}) returns as 1 (Monday), we tell this field to subtract 3 days from the {Go-Live Date} <<DATEADD({Go-Live Date},-3,'days')>> and return that date – which is the Friday prior.

And so on, for any “Weekday” value that gets returned.

[Nicole_Flewelle]

That worked like a charm! Thank you!

[W_Vann_Hall]

Here’s an alternative method, mainly because I was curious if it would work. The formula works by determining the week of the year and the week year for a date 5 days prior to the {Go-Live Date} and then calculating the date of Friday for that week and week year.¹ As is usually the case with date-shifting formulas, getting it to work for 51 weeks of the year (52 for leap years) was simple; getting it to work for the remaining week, the first week of January, was a royal pain. The first half of that battle was won when I realized I needed to use some of the less-frequently seen format specifiers — namely, ISO week of the year and ISO week year. The second half I finally had to brute-force, until I realized I was subtracting too many days in my DATEADD() function,

I followed @Jeremy_Oglesby’s lead in assuming the previous Friday for a {Go-Live Date} that falls on a Saturday is the day before, rather than wrapping back to the Friday 8 days before. If that’s not the case, the formula can be tweaked by changing the number of days subtracted by DATEADD().

This has worked for every date value I’ve tossed at it, including early January dates for both regular and leap years:

DATETIME_PARSE(
    '5 '&
        DATETIME_FORMAT(
            DATEADD(
                {Go-Live Date},
                -5,
                'd'
                ),
            'W GGGG'
            ),
    'E W GGGG'
    )

There’s no real advantage to using this approach versus Jeremy’s, although it might be slightly easier to maintain in cases where dates are shifted by more than a week. (It also might be made to support the WORKDAY() slightly more easily, as well.) Again, I was curious whether one could specify something like ‘Friday of week 47’ and have Airtable return the correct answer.

---

1. The ISO week year is a somewhat confusing beast that exists precisely so such calculations can be made. Depending on what day December 31 and January 1 fall, the week year for the last few or first few days of the year may not match the calendar year. For instance, 12/30/2018 is in week 52 of week year 2018, but 12/31/2018 falls in week 1 of week year 2019. It’s even more confusing in practice, because which day of the week is considered Day 1 is determined by one’s locale; the formula should work for most Western locales but will need to be modified for use in cultures where the week begins on Monday, rather than Sunday.