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.

 

[Dave87]

OK, this should work
assuming your data is in column A starting in cell A2
in column B add

=TEXT(A2,"ddd-mmm")

in column C add

=IF(AND(TEXT(A2,"ddd")="Wed",COUNTIF($B$2:B2,"Wed-"&TEXT(A2,"mmm"))=3),"Third Wednesday","")

You can then create a pivot table or filter your data to neatly summarise your data

(Apologies for not posting in XL2BB but work have blocked it, i hope using the VBA code instead has made it clear)

 

[DJFANDANGO]

Try this...

In A1 put the first month you want, example: 1/1/2020.

Then in C1 I put this formula:

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

Then in C2 I put:

=DATE(YEAR(C1),MONTH(C1)+1,22)-WEEKDAY(DATE(YEAR(C1),MONTH(C1)+1,4))

Then drag down...

 

[kweaver]

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)

 

[arniebun]

thank you so much I tried all solution only 1 of them just give me a blank cell which was dave87's but thank you all so much for responding. this is by far the best excel help board which makes me stay loyal to this one. thanks again you all have help me so much.

 

[Dave87]

thank you so much I tried all solution only 1 of them just give me a blank cell which was dave87's but thank you all so much for responding. this is by far the best excel help board which makes me stay loyal to this one. thanks again you all have help me so much.

did you drag the formula down?

 

[Dave Patton]

Try either formula

T202007b.xlsm
	A	B	C
1	01-Jan-20	Wed 15-Jan-20	Wed 15-Jan-20
2	01-Feb-20	Wed 19-Feb-20	Wed 19-Feb-20
3	01-Mar-20	Wed 18-Mar-20	Wed 18-Mar-20
4	01-Apr-20	Wed 15-Apr-20	Wed 15-Apr-20
5	01-May-20	Wed 20-May-20	Wed 20-May-20
6	01-Jun-20	Wed 17-Jun-20	Wed 17-Jun-20
7	01-Jul-20	Wed 15-Jul-20	Wed 15-Jul-20
8	01-Aug-20	Wed 19-Aug-20	Wed 19-Aug-20
9	01-Sep-20	Wed 16-Sep-20	Wed 16-Sep-20
3b

Cell Formulas
Range	Formula
B1:B9	B1	=DATE(YEAR(A1),MONTH(A1),22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),4))
C1:C9	C1	=WORKDAY.INTL(A1-1,3,"1101111")
A2:A9	A2	=EDATE(A1,1)

 

[kweaver]

I looks to me like my simple formula finds the 3rd Wednesday as well. Why doesn't it?

Book1
	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
5	1/1/2020	1/15/2020
6	2/1/2020	2/19/2020
7	3/1/2020	3/18/2020
8	4/1/2020	4/15/2020
9	7/1/2020	7/15/2020
Sheet1

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

 

[Dave Patton]

It looks like three of the suggestions provide the correct answer. You can use whichever one you prefer.

T202007b.xlsm
	A	B	C	D
1		DJFANDANGO	Dave Patton	Kweaver
2	01-Jan-20	Wed 15-Jan-20	Wed 15-Jan-20	Wed 15-Jan-20
3	01-Feb-20	Wed 19-Feb-20	Wed 19-Feb-20	Wed 19-Feb-20
4	01-Mar-20	Wed 18-Mar-20	Wed 18-Mar-20	Wed 18-Mar-20
5	01-Apr-20	Wed 15-Apr-20	Wed 15-Apr-20	Wed 15-Apr-20
6	01-May-20	Wed 20-May-20	Wed 20-May-20	Wed 20-May-20
7	01-Jun-20	Wed 17-Jun-20	Wed 17-Jun-20	Wed 17-Jun-20
8	01-Jul-20	Wed 15-Jul-20	Wed 15-Jul-20	Wed 15-Jul-20
9	01-Aug-20	Wed 19-Aug-20	Wed 19-Aug-20	Wed 19-Aug-20
10	01-Sep-20	Wed 16-Sep-20	Wed 16-Sep-20	Wed 16-Sep-20
11
3b

Cell Formulas
Range	Formula
B2:B10	B2	=DATE(YEAR(A2),MONTH(A2),22)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),4))
C2:C10	C2	=WORKDAY.INTL(A2-1,3,"1101111")
D2:D10	D2	=A2+21-WEEKDAY(A2+3)
A3:A10	A3	=EDATE(A2,1)

 

[arniebun]

it might have been my excel or as ding it wrong, i tryied it again and it worked. thanks