Recurring Date Formula that can tell the date of a weekday

[Max_Ha]

Hi everybody.

My question is simple… (hopefully the solution also)
I need a formula who automaticly tells me what date it is next Tuesday. On Wednesday it needs to update itself to next Tuesday.

Thanks in advance!

[Tim_Sullivan]

Hi @Max_Ha,

Your question wasn’t too difficult to answer.

In order to calculate the days, first we have to determine the number value of the day of the week. I used this formula to figure that out:

WEEKDAY({Today’s Date})

Monday is 1, Tuesday is 2, Weds is 3… etc.
Next Tuesday would be “2 + 7” or 9.
Let’s calculate how may days it is until next Tuesday:

Days until Next Tuesday:

IF({Weekday Today}>2,9-{Weekday Today},2-{Weekday Today})

Now, we just have to do some simple math to add the number of days until Tuesday to today’s date:

DATEADD({Today’s Date},{Days until Next Tuesday},‘days’)

To avoid errors in the calculation due to time zone differences, make sure to flip the switch to use the same time zone for all collaborators.

I downloaded this formula testing table just a little bit ago from a post by another user and added a tab for your calculation. I broke out the steps to make it easy to understand, but you could do it all in one formula field.

Airtable

Formula Testing - Airtable

Explore the "Formula Testing" base on Airtable.

[Max_Ha]

Hi @Tim_Sullivan ,

Thank you for your answer. I managed to figure it out by your example. I also tried to combine all the formulas in one field. But that didn’t go so wel :winking_face:

There is still one little detail I was wondering you could help me with…

Right now the field is telling me wich date it is next Tuesday. But if today is also a Tuesday, I would rather have it tell me which date it is today then next weeks Tuesday.
I was thinking of maybe adding an IF statement. But I don’t know which field or what to state…

Hopefully you know the answer.

Thanks in advance!

[Max_Ha · Accepted solution]

Nevermind, I’m a genius :rofl:

I’ve added the following statement to the “Next Tuesday’s Date” field.

IF({WEEKDAY} = “2” , {Today’s Date} , DATEADD({Today’s Date}, {Days until Next Tuesday}, ‘DAY’))

:metal: