Calculating Dates

[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

[jeffreybrown]

Does this work for you?

http://www.tek-tips.com/faqs.cfm?fid=7549

Please Login or Register  to view this content.

[StevenAlberta]

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

[daddylonglegs]

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

[StevenAlberta]

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

[jeffreybrown]

Hi Steven,

Did you see this part?

If you want the last Monday in a month then find the first Monday in the next month and subtract 7

Try

Please Login or Register  to view this content.

[StevenAlberta]

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.

[jeffreybrown]

Did you try the solution in post #6?

It produces >> 5/28/2012 >> Monday

[StevenAlberta]

I found the problem. I was not adding one month to the Weekday part. It now works.

Thanks so much,
Steven

[daddylonglegs]

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