Re: Airtable Blog "closest Tuesday to given day" Formula Incorrect?

408 0
cancel
Showing results for
Did you mean:
4 - Data Explorer

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:

(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?

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.