Help

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.

Formula to subtract days from date to closest specific weekday

Topic Labels: Formulas
706 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

3 Replies 3

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