Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What excel formula can I use to calculate the second Monday of a given month?

You can assume I have a cell containing the first day of the month to work with.

In the interests of other users, can you please also explain how to alter the formula to get the 1st, 3rd or 4th Monday of the month too, and other days of the week too. eg the third Friday of the Monday...

share|improve this question
up vote 6 down vote accepted

Here's something I found with a quick search:

Generically you can get the nth xday of the month with this formula

=DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday)) 

where year is in B2 and month (as a number 1 to 12) is in A2, and where xday is a number representing the day of the week (1 = Sun through to 7 = Sat), so for 1st Saturday that becomes

=DATE(B2,A2,1+7*1)-WEEKDAY(DATE(B2,A2,8-7)) 

or for 4th Thursday

=DATE(B2,A2,1+7*4)-WEEKDAY(DATE(B2,A2,8-5)) 

So, if I'm reading this properly, the 2nd Monday of the month is

=DATE(B2,A2,1+7*2)-WEEKDAY(DATE(B2,A2,8-2)) 

(credit where credit is due)

share|improve this answer
1  
It works perfectly, as far as I can tell! Thanks very much – Highly Irregular Nov 21 '11 at 22:37

Greetings: I wanted a formula to compute the second Wednesday of the current month and year to use to automatically add the date of the meeting to a sign in sheet for a monthly meeting. I used the great information in this post and created the formula below.

=DATE(YEAR(NOW()),MONTH(NOW()),1+7*2)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),8-4))

Thanks for the great information.

share|improve this answer
    
Welcome to Super User. Please don't post an answer to confirm that another answer worked. The site's Q&A format reserves answers for solutions to the question, and each answer should contribute another solution. The way to indicate that an answer was useful is to invest a little time in the site and you will gain sufficient privileges to upvote answers you like. – fixer1234 2 days ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.