Jan 03, 2021 08:49 AM
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!
Jan 03, 2021 09:15 AM
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.
Jan 06, 2021 03:39 PM
Yes - that worked. Thank you!
Jan 07, 2021 08:44 AM
Great, go ahead and mark my first reply as the solution to close the thread