Find the next occurrence of a weekly event

[bt109]

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.

[joshsorenson]

Couldn't you add seven days?

[bt109]

That

[Dominic]

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'
),
""
)