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
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
Last edited by JO505; 03-19-2013 at 04:54 PM.
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))
Life's a spreadsheet, Excel!
Say thanks, Click *
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
Last edited by daddylonglegs; 03-19-2013 at 03:24 PM.
Audere est facere
Thank you both. They work but I have no idea what I am looking at within the formulas. If I could get some explanation I should be able to modify it for possible future use.
Thanks
Jim O
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
Last edited by daddylonglegs; 03-19-2013 at 04:30 PM.
DLL,
Thank you for your time and expertize with this. So much to digest. This is a great formum.
Again thanks to both
Jim O
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks