Formula to subtract days from date to closest specific weekday

[Lauren_Noce]

Hello! I’m new to airtable and trying to figure out what formula will take a date field and then subtract approximately 14 days to the next closest specific weekday.

Details -
Current Formula only calculates date 2 weeks prior: DATEADD({Event Date}, -2,‘week’)
I need to back up from the “Event Date” approximately 2 weeks to pick-up product for the event, but pickups always need to be on a Wednesday.

Any help is much appreciated!

[Kamille_Parks]

Try:

IF(
   {Event Date},
   DATEADD(
      {Event Date}, 
      (- 14 - (WEEKDAY({Event Date}) - 3)), 
      'days'
   )
)

WEEKDAY() will give you a numeric value of the day of week, somewhere between 0 and 6; the value for Wednesday is 3. The formula above will get the date that is 14 days (two weeks) before the {Event Date} and then find the closest Wednesday to it.

[Lauren_Noce]

Kamille_Parks:

IF(
   {Event Date},
   DATEADD(
      {Event Date}, 
      (- 14 - (WEEKDAY({Event Date}) - 3)), 
      'days'
   )
)

Yes - that worked. Thank you!

[Kamille_Parks]

Great, go ahead and mark my first reply as the solution to close the thread