# Calculate Date of Previous Week's Friday

7004 3
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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

3 Replies 3
14 - Jupiter

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.

6 - Interface Innovator

That worked like a charm! Thank you!

13 - Mars

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(