LinkBack URL
About LinkBacks
StevenAlberta 
I am trying to find an easy way to have Excel calculate dates. For example, I want to calculate the date of Columbus Day. It is the 2nd Monday in October. How do I find the 2nd Monday in October?
Thanks, Steven
I am trying to find an easy way to have Excel calculate dates. For example, I want to calculate the date of Columbus Day. It is the 2nd Monday in October. How do I find the 2nd Monday in October?
Thanks, Steven
Does this work for you?
http://www.tek-tips.com/faqs.cfm?fid=7549
Formula:
=DATE(YEAR(TODAY()),10,CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),10,1)),9,8,14,13,12,11,10))
Last edited by jeffreybrown; 07-05-2012 at 06:32 PM.
HTH
Regards, Jeff
I am looking for something where, within my formula, I can input the year, month, weekday and the weekdays position within the month (3rd Mon). For example, I want to find the 3rd Mon in July 2014.
Any ideas.
Thanks, Steven
generically you can use this formula
=DATE(yr,mon,1+pos*7)-WEEKDAY(DATE(yr,mon,8-day))
where yr = year, mon = month, pos = position (1,2,3 or 4) and day = day of week Sun =1 through to Sat =7
So for your example Columbus Day this year would be
=DATE(2012,10,1+2*7)-WEEKDAY(DATE(2012,10,8-2))
which gives you Monday 8th October 2012
...or 3rd Monday in July 2014
=DATE(2014,7,1+3*7)-WEEKDAY(DATE(2014,7,8-2))
which gives you Monday 21st July 2014
If you want the last Monday in a month then find the first Monday in the next month and subtract 7
Audere est facere
Thanks for the code. It works great but I am having problems altering the code to find the last day of Week for a particular month. For example, to find the last Monday in May. How would I do that.
Thanks, Steven
Hi Steven,
Did you see this part?
TryOriginally Posted by daddylonglegs
Formula:=DATE(2012,6,1+1*7)-WEEKDAY(DATE(2012,6,8-2))-7
Last edited by jeffreybrown; 07-06-2012 at 07:02 PM.
Yes. I have tried the following but does not work:
=DATE(yr,mon + 1,1+1*7)-WEEKDAY(DATE(yr,mon,8-day))-Day(7)
I added one month to the month, exchanged the pos with 1 and subtracted 7 days.
Did you try the solution in post #6?
It produces >> 5/28/2012 >> Monday
I found the problem. I was not adding one month to the Weekday part. It now works.
Thanks so much,
Steven
You don't really need DAY(7) at the end [that could give you the wrong result in some circumstances] - better to just use 7
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks