A formula to calculate the third Wednesday of the month.

[arniebun]

I have loads of data with 01/01/2013 01/02/2014 and so on till present day. I need to calculate the third Wednesday of each month, can anyone please help. a formula would be ideal. please please help me, its a nightmare trying to do it manually.

 

[Rick Rothstein]

Here is a generic formula to calculate the nth day of a given month in a given year...

=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

where Nth is the number you want 1st, 2nd, 3rd etc and where DoW stands for day of the week with Sunday starting with 1, Monday with 2 and so on. For example, if you want the 3rd Wednesday in the month for the date in cell A1 it would look like

=DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-4))

Of course, you can complete the mathematical operations to "neaten up" the formula...

=DATE(YEAR(A1),MONTH(A1),22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),4))

 

[arniebun]

thank you Rick great solution

 

[redinator]

I have loads of data with 01/01/2013 01/02/2014 and so on till present day. I need to calculate the third Wednesday of each month, can anyone please help. a formula would be ideal. please please help me, its a nightmare trying to do it manually.

Just in case anyone still needs a simple answer to this question, as an example I used Wednesday October 20th, 2021 in A1 and Wednesday November 17, 2021 in A2 (two sequential dates occurring on the third Wednesday) then selected both cells and dragged down to get the resulting list of dates. If you wanted the third Thursday of each month; you'd use Thursday October 21st, 2021 and Thursday November 18, 2021.

 

[KYCowboy]

Try

Amort_Sample.xlsx
	A	B
1	Date	3rd Wed.
2	7/1/2020	7/15/2020
3	8/1/2020	8/19/2020
4	12/1/2017	12/20/2017
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=A2+21-WEEKDAY(A2+3)

This very simple and straight-forward formula worked perfectly for me! Many thanks.

- Tim -