Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Recurring Date Formula that can tell the date of a weekday

Topic Labels: Formulas
Solved
Jump to Solution
2371 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Max_Ha
6 - Interface Innovator
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
Max_Ha
6 - Interface Innovator
6 - Interface Innovator

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:

See Solution in Thread

3 Replies 3
Tim_Sullivan
7 - App Architect
7 - App Architect

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.

image

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.

Max_Ha
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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: