Formula to subtract days from date to closest specific weekday

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!

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.

Yes - that worked. Thank you!

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

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.