Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 11, 2023 11:08 PM
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.
Apr 13, 2023 08:49 PM
Couldn't you add seven days?
Apr 18, 2023 05:10 PM
Apr 24, 2023 12:01 AM
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'
),
""
)