MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dates


Posted by Scott Currie on May 10, 2001 6:39 AM

I am trying to refer every third Wednesday to a formula and cant figure it out. I have been helped before with an every 2nd Tuesday formula but I cant seem to figure it out.
Any help?


Posted by Mark W. on May 10, 2001 7:14 AM

What's your every 2nd Tuesday formula?

Posted by Scott Currie on May 10, 2001 7:20 AM


Mark,
This is the one you helped me with last month or so...
=DATE(YEAR(I13),MONTH(I13)+1,1)+CHOOSE(WEEKDAY(DATE(YEAR(I13),MONTH(I13)+1,1)),9,8,7,13,12,11,10)
Now I have to switch to every third wednesday.
Thanks for the help.

Posted by Mark W. on May 10, 2001 1:56 PM

Just add 8 to each of the CHOOSE() argument after
the WEEKDAY() function. ={9,8,7,13,12,11,10}+8
returns {17,16,15,21,20,19,18}, so the new formula
would be:

=DATE(YEAR(I13),MONTH(I13)+1,1)+CHOOSE(WEEKDAY(DATE(YEAR(I13),MONTH(I13)+1,1)),17,16,15,21,20,19,18)

Posted by Kevin James on May 10, 2001 2:57 PM

Scott:

If you'd like a sheet that allows you the flexability to choose, month, week in month and day in week, download it at:

whitehare.topcities.com/Downloads.html

The file is Date4DOW.xls

Kevin