Formula to reflect the 4th Wed. of the current month.

I am looking for a formula that will display the 4th Wed. of the current month. As an example, the forth Wed. of this month is 3/27/2013, on the 28th of the month (the next month) is should display 4/24/2013, and change each preceeding month.

Jim O

This will give you 4th wednesday of each month

=DATE(YEAR(B1),MONTH(B1),21+CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1),1)),4,3,2,1,7,6,5))

Hello Jim,

If you have today's date in A2 then this formula will give you the next "4th Wednesday", so today it will give you 27th March....but change A2 to 28th March 2013 and it gives 24th April as stipulated

=LOOKUP(A2-1,EOMONTH(A2,{-2,-1;-1,0})+29-WEEKDAY(EOMONTH(A2,{-2,-1;-1,0})+4))

If you want to use that formula in Excel 2000 then you'll need Analysis ToolPak installed for EOMONTH function. Replace A2 in the formula with TODAY() if you don't want to use a cell with today's date

generically you can use this formula to find the nth xday of any month

=DATE(year,mon,1+7*n)-WEEKDAY(DATE(year,mon,8-xday))

where xday is a number representing the day 1 = Sun through to 7 = Sat

so, for instance, you can get the 4th Wednesday of any month by using

=DATE(year,month,1+7*4)-WEEKDAY(DATE(year,month,8-4))

which simplifies to

=DATE(year,month,29)-WEEKDAY(DATE(year,month,4))

Now because you want to extend that to get the next 4th Wednesday it will be easier to use EOMONTH, so using the same logic you can get the 4th Wed of any month by taking the 29th of that month and subtracting the WEEKDAY value of the 4th of the month so that's

=EOMONTH(Date,-1)+29-WEEKDAY(EOMONTH(Date,-1)+4)

Now the tricky part is to convert that to a formula that will give you the 4th Wednesday of this month before that 4th Wednesday.....but the 4th Wednesday of next month if the current month's 4th Wednesday has passed.

To do that we can construct a mini "lookup table" so if column 1 of the table consists of last month's 4th Wed and this months 4th Wed and the second column is this month's and next month's then we can lookup the date against that table - if the date is before the current months 4th wed then it will match with last month and return this month.......but if it's on or after this month's 4th Wed it will match with this month and return next month's (!)

So this part

=EOMONTH(A2,{-2,-1;-1,0})+29-WEEKDAY(EOMONTH(A2,{-2,-1;-1,0})+4)

creates that lookup table

If you paste that formula in a cell and then press F2 and F9 then you get this result when A2 is today's date

={41332,41360;41360,41388}

those numbers are the serial numbers that represent 41332 = Wed 27th Feb 2013, 41360 = Wed 27th Mar 2013, 41388 = Wed 24th April 2013

so we can use that formula as the basis for a LOOKUP like this

=LOOKUP(A2-1,EOMONTH(A2,{-2,-1;-1,0})+29-WEEKDAY(EOMONTH(A2,{-2,-1;-1,0})+4))

I use A2-1 because you seemed to want the current 4th Wednesday on the actual date itself (so if A2 is 27th March 2013 the formula returns 27th March 2013) - if you want it to return 24th April 2013 on that date then the -1 isn't required