Formula to get Second Wednesday of the Month

[privatemoon]

Hi,

I'm trying to get the date for the second Wednesday of the month. The formula I currently have is:
=DATE(YEAR(NOW()),MONTH(NOW()),1+7*2)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),8-4))

However, that gives me 9/8/21 for this month, which is the first Wednesday of the month. Any help will be appreciated, thanks!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The 8th is the 2nd Wednesday as the 1st is a Wednesday.

 

[privatemoon]

I'm currently using Office 365, thanks! I went ahead and updated my Account details.

I can't believe I didn't see that, thank you for bringing that up. The intent of the formula should grab the Wednesday following the second Tuesday of every month. So 9/15/21 in this case.

 

[Fluff]

I went ahead and updated my Account details.

Thanks for that, how about

=WORKDAY.INTL(EOMONTH(TODAY(),-1),2,"1011111")+1

 

[privatemoon]

The formula works great, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[GJL]

Here's another way which to my mind is more intuitive, and easier to adjust for different nth days and different weekdays:

=A1+(4+7+7)-WEEKDAY(A1)-IF(WEEKDAY(A1)<=4,7,0)

Where A1 is a date with the first of the desired month, such as April 1st. If you want it to be for the current month, the formula in A1 can look like this:

=DATE(YEAR(NOW()),MONTH(NOW()),1)

For different nth days, add or remove 7s from the first part of the formula.

For different weekdays, adjust the two 4s in the formula. Wednesday is 4, Tuesday is 3, Thursday is 5, etc.

For example, here's the third Tuesday. Compared to the example above for the 2nd Wednesday, I added a third 7 to the first part of the formula and changed the two 4s to 3s:

=A1+(3+7+7+7)-WEEKDAY(A1)-IF(WEEKDAY(A1)<=3,7,0)

And here's the first Thursday. Compared to the example for the 2nd Wednesday, I removed a 7 from the first part of the formula and changed the two 4s to 5s:

=A1+(5+7)-WEEKDAY(A1)-IF(WEEKDAY(A1)<=5,7,0)

 

[Fluff]

Here's another way which to my mind is more intuitive, and easier to adjust for different nth days and different weekdays:

That is something we would have to disagree on. I would have to say you solution is neither more intuitive, nor easier to adjust.