For an email marketing series, I needed to calculate **every** nth day of the week.

For instance, I needed the date every Wednesday for 5 weeks, starting from the next upcoming Wednesday.

It took a while, but this formula can adjust for months with 4 or 5 instances of a day, months of various lengths, leap years, and periods that need to change the year between the calculation and the next Wednesday.

Because this is for marketing and I want to get a chance to nurture someone before the next date, if the registration happens on a Wednesday, it calculates the date of the *next* Wednesday, not the current one.

Itâ€™s a bit hairy and it gave me a stout headache, but here it is.

Ok, letâ€™s break this down.

The entire variables consists of 5 nested if(; statements.

The first part of the if statement finds the current day of the month as a numeral (so April 4 would be â€ś4â€ť) and asks if that number is bigger then the next formula, which finds the day of month of the first Wednesday in that month.

This is the section of the formula that finds the date of the first Wednesday.

The first formatDate( grabs the YYYY-MM of the current month, and the -1 sets the day to the first day of the month. The outer formatDate grabs that first day of the month and reports back an abbreviated day of the week (Sun, Mon, Tue, etc).

Since I want the next Wednesday, the Switch function swaps the abbreviated day with the number of days I would have to add to that day to get to Wednesday. So, if the formula is trigger on Sun, we would need to add 4 days to get to Tuesday. And since Sunday is the first day of that month, the next Tuesday is the first Tuesday of the month.

Now I have both the current day of the month as a numeral and the first Wednesday of the month as a numeral. If the current date is smaller (before) the first Wednesday, the date is set for that first Wednesday.

If the day of the month is after the first Wednesday, then it moves to the next formula.

That next formula is very similar.

It once again grabs the current day of the month and asks if that number is equal to or less than the second Wednesday of the month.

That second Wednesday is calculated by using the same formula that found the first Wednesday and adding 7 (getting to the second Wednesday of the month).

The date isnâ€™t before the first Wednesday (we ruled that out in the first if(, so if itâ€™s before the second Wednesday then that Wednesday must be the date I want.

The third if( adds 14 to the first Wednesday and the fourth if( adds 21 to the first Wednesday.

If it gets past the 4th if(, which asks if the current date is before the 4th Wednesday of the month, and fails the if(, then we take the date calculation of the 4th Wednesday and add 7 days to it, which will wrap us back around to the first Wednesday of the following month (or year) without needing to calculate the number of days in a month or manually accounting for a year.

Hereâ€™s the JSON if anyone like to use this.

I included a few other breakdowns I used while developing this formula as well.

Time and Date Calculation.json (17.4 KB)