3

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.

evilrod
  • 77
  • 1
  • 1
  • 10

2 Answers2

4

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you very much Scott that does the work. Would you please give me the formulas for every second wednesdays of the month as well please. – evilrod Jul 28 '16 at 14:47
  • Ummmm....Didn't @Jeeped already do that for you? But in mine change the `22` to `15` and for the first to `8` and to the forth `29`. You need to mark one of the answers as correct. The idea is that it goes to the day after the full week in which you are looking then goes back till it finds wednesday. – Scott Craner Jul 28 '16 at 14:51
  • Thank Scott, my reputation is under 12 so it's only recorded not changing the rating. Thank you for everything – evilrod Jul 28 '16 at 15:37
  • You click the check mark by the answer to mark as correct. Your rep does not matter for that. @TommyNG – Scott Craner Jul 28 '16 at 15:37
2

With the first of each month in A2 (e.g. =DATE(2016, ROW(1:1), 1) filled down, put this into B2 and fill right and down.

=$A2+(7-WEEKDAY($A2,14))+(COLUMN(A:A)-1)*7

third_wednesday

The conditional fifth Wednesday changes the formula in A2 to,

=IF(MONTH($A2+(7-WEEKDAY($A2,14))+(COLUMN(A:A)-1)*7)=MONTH($A2), $A2+(7-WEEKDAY($A2,14))+(COLUMN(A:A)-1)*7, "")