List of 3rd Wednesdays of each month

excel date excel-formula weekday
evilrod picture evilrod · Jul 27, 2016 · Viewed 10.1k times · Source

I'm trying to find the formula to automatically generate the Third Wednesday of Each month.I searched and found this: 

=LOOKUP(A3,DATE(YEAR(A3),{0,3,6,9,12;3,6,9,12,15},22)-WEEKDAY(DATE(YEAR(A3),{0,3,6,9,12;3,6,9,12,15},4)))

Where my first Wednesday is in A3. However this formula only give the results for 3, 6, 9 and 12 months. Would anyone please give me a formula to get all year 12 months and keep going to the following year if I drag down.

Answer

Scott Craner picture Scott Craner · Jul 27, 2016

In A3 I put the first month I want, I put 1/1/2016.

Then in C3 I put this formula:

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

Then under C3 in C4 I put:

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

Then drag down.

enter image description here

Related questions

How do I convert a calendar week into a date in Excel?

I have a week number and a year, and would like to calculate the date of the Monday for that specific week in Microsoft Excel. Year Week Date (Monday) 2012 1 January 2, 2012 2013 16 April 15, 2013 2014 42 October 13, 2014 What formula can I use to convert a …

Read More
SUMIFS with date and name criteria... month and year only

I am trying to get a SUMIFS formula to check a column of dates and sum only the values that correspond to the matching year and month of the criterion date. I would also like this SUMIFS to include a …

Read More
Sumif between two dates over two columns

I would like to sum a column between two dates, however the dates are in separate columns. I want the sum total between the following dates 01-01-2014 (cell E1) and 31-03-2014 (cell F1). If you look at cell …

Read More