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

[Dennis_Petrou]

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!

[Kamille_Parks]

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(
   DATEADD(
      {Due Date}, 
      SWITCH(WEEKDAY({Due Date}), 0, -2, 6, -1, 0), 
      'days'
   ), 
   TODAY(), 
   'days'
)

[Dennis_Petrou]

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!

[Kamille_Parks]

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

Dennis_Petrou:

So if the launch day is set to August 31 the Due Day should be Aug 1st

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.

[Dennis_Petrou]

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.

[Kamille_Parks · Accepted solution]

DATEADD(
   DATEADD({Launch Date}, -30, 'days'),
   SWITCH(
      WEEKDAY(
         DATEADD({Launch Date}, -30, 'days')
      ), 
      0, -1, 
      1, -2, 
      0
   ), 
   'days'
)

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

[Justin_Barrett]

@Dennis_Petrou Did @Kamille_Parks provide the answer you were seeking? If so, please mark her comment above as the solution to your question. This helps others who may be searching with similar questions. Thanks!