Skip to main content

The following formula in the Airtable blog post here, under the heading “Find the next occurrence of a specific day :hourglass_flowing_sand: ” with the description:

“if you just need to know the nearest Tuesday to a given date—even if that Tuesday has already passed—this simpler formula will do the trick:”

is as follows:

SET_TIMEZONE(DATEADD({Date},2-(WEEKDAY({Date})),‘days’),‘America/Los_Angeles’)

(It is suggested that the 2 in the formula relates to finding the nearest “Tuesday”, and can be replaced with any other day from Sunday (0) --> Saturday (6) )

I noticed this formula break when the given date is a Sunday and you are looking for the nearest Thursday (4), Friday
(5) or Saturday (6) (which would occur before the given date).

Since WEEKDAY({given date}) of a Sunday resolves to 0, The formula will never return a day in the past.

More generally,

  • if given date is Monday (1), this function will never return a day more than 1 day in the past (e.g. Saturday)
  • if given date is Tuesday (2), this function will never return a day more than 2days in the past etc.

Unless I am misunderstanding the blog post, this formula just appears to be wrong? I know its been a while since it was posted, but I can’t imagine that this ever worked (or should stay published as is)?

Can someone confirm this?

Welcome to the Airtable community!

Good catch! You are correct that the formula does not always produce the “closest Tuesday”.

The formula always returns a date with the given day of the week, but in the same week as the original date. It does not give the “closest” day.

Sometimes the date returned is before the given date, and sometimes is is after the given date. This is because the DATEADD function can add a negative number, and sometimes the ,2-(WEEKDAY({Date})) evaluates to a negative number.

You can report the error in the blog post to Airtable support. When I have reported errors in formulas on support pages, Airtable has fixed them fairly quickly. I don’t know if they will be able to fix blog posts as quickly, but the best way to find out is to report it.

Terms & ConditionsAccessibility statement