Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 17, 2020 08:17 AM
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!
Solved! Go to Solution.
Sep 23, 2020 04:00 AM
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:
Sep 17, 2020 10:31 AM
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.
Sep 23, 2020 03:47 AM
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!
Sep 23, 2020 04:00 AM
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: