# Calculate Date of Previous Week's Friday

#1

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)?

#2

This should work for you, Nicole:

``````SWITCH(
WEEKDAY({Go-Live Date}),
)
``````

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.

#3

That worked like a charm! Thank you!

#4

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(
{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.