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.
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!
Yes - that worked. Thank you!
Great, go ahead and mark my first reply as the solution to close the thread
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.