Help

Re: Find the next occurrence of a weekly event

590 0
cancel
Showing results for 
Search instead for 
Did you mean: 
bt109
4 - Data Explorer
4 - Data Explorer

I do a lot of networking for my job, and many networking groups that I visit have weekly meetings. I can't attend all of them every week, but I like to keep track of their meetings in case I can use them fill a gap in my schedule. To do this, I would like to have a formula that allows me to enter the day of the week (i.e. Wednesdays), and it calculates the next upcoming date (the 12th).

I've found a formula that will calculate the next Tuesday from a given date, and I think I can retool it so it will calculate the next Tuesday from today, but I need one that allows for variation in the day of the week, so that it will calculate the next Tuesday for a group that meets Tuesdays and the next Wednesday for groups that meet Wednesdays, etc.

 

3 Replies 3
joshsorenson
6 - Interface Innovator
6 - Interface Innovator

Couldn't you add seven days?

That
Dominic
6 - Interface Innovator
6 - Interface Innovator

Hi,

The following formula should work if you have a field called "Day of Week" which has the day you are wanting to see the next upcoming date for. So, to display the date for the next Monday, make sure the value for the  "Day of Week" field is "Monday". You can add a DATETIME_FORMAT() if you wanted to further format the output date to a string, like "DD/MM/YYYY" for example.

 

IF(
{Day of Week},
DATEADD(
TODAY(),
IF(
WEEKDAY(TODAY()) > SWITCH(
{Day of Week},
"Sunday", 0,
"Monday", 1,
"Tuesday", 2,
"Wednesday", 3,
"Thursday", 4,
"Friday", 5,
"Saturday", 6
),
7 - WEEKDAY(TODAY()) + SWITCH(
{Day of Week},
"Sunday", 0,
"Monday", 1,
"Tuesday", 2,
"Wednesday", 3,
"Thursday", 4,
"Friday", 5,
"Saturday", 6
),
SWITCH(
{Day of Week},
"Sunday", 0,
"Monday", 1,
"Tuesday", 2,
"Wednesday", 3,
"Thursday", 4,
"Friday", 5,
"Saturday", 6
) - WEEKDAY(TODAY())
),
'days'
),
""
)