Skip to main content
Solved

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

  • July 29, 2021
  • 6 replies
  • 67 views
D
Forum|alt.badge.img+12

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!

Best answer by Kamille_Parks11

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.


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)

6 replies

Kamille_Parks11
Forum|alt.badge.img+27

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'
)
Julian_E_Post
D
Forum|alt.badge.img+12
  • Dennis_Petrou
  • Author
  • Known Participant
  • July 29, 2021

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

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_Parks11
Forum|alt.badge.img+27

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!


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.

D
Forum|alt.badge.img+12
  • Dennis_Petrou
  • Author
  • Known Participant
  • July 29, 2021

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.


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_Parks11
Forum|alt.badge.img+27

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.


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)

Databaser
H
TC_
Justin_Barrett
Forum|alt.badge.img+21

@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!

M
Terms & ConditionsAccessibility statement