Help

Formula to subtract days from date to closest specific weekday

Topic Labels: Formulas
2053 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Lauren_Noce
4 - Data Explorer
4 - Data Explorer

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