Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# Calculate days, but if it falls on weekend choose the friday before

Topic Labels: Formulas
Solved
951 6
cancel
Showing results for
Did you mean:
7 - App Architect

Hello!
I am trying to make a formula to calculate X number of days before a Launch Date. I do not want to exclude weekends, however if the Due Date happens to fall on a weekend, I want to make it that Friday instead.

I know about the DATEADD({Launch Date},-7,‘days) formula, and I’ve seen the WORKDAY function but not exactly sure how to make this work.

So when calculating the total days before launch I DO want to include any weekends, but if the Due date falls on a weekend, it should add an extra day or two to make it that Friday…

Thanks!

1 Solution

Accepted Solutions
16 - Uranus
SWITCH(
WEEKDAY(
),
0, -1,
1, -2,
0
),
'days'
)

(turn off “Use the same time zone (GMT) for all collaborators” in the field formatting options)

6 Replies 6
16 - Uranus

The following formula will calculate the number of days between today and the due date unless the due date is a Saturday or Sunday, otherwise its the difference between today and the last Friday before the due date.

DATETIME_DIFF(
{Due Date},
SWITCH(WEEKDAY({Due Date}), 0, -2, 6, -1, 0),
'days'
),
TODAY(),
'days'
)
7 - App Architect

Thank you for this! One more question… if I want the calculation to not be from today, but from a Launch Date field… would I just swap out TODAY with {Launch Day} or something like that?

So if the launch day is set to August 31 the Due Day should be Aug 1st, unless it’s a weekend…

Thank you for your help, I’m still learning!

16 - Uranus

Yes, you would replace TODAY() with {Launch Date}.

To clarify, this formula is not calculating a Due Date. It assumes you already know the due date and is calculating the number of days between the launch date and the due date.

7 - App Architect

Oh I see.

I was looking for it to tell me the Due Date if I only know the Launch Date… where the due date is 30 days before the launch date unless its a weekend. Sorry for the confusion and thanks for your help.

16 - Uranus
SWITCH(
WEEKDAY(