15

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...

1
  • 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 created the formula below. =DATE(YEAR(NOW()),MONTH(NOW()),1+7*2)-WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),8-4))
    – user554112
    Feb 4, 2016 at 18:44

3 Answers 3

25

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)

1
  • 1
    It works perfectly, as far as I can tell! Thanks very much Nov 21, 2011 at 22:37
0

=IF((WEEKDAY(A1) > 3),(A1+(17-WEEKDAY(A1))),(A1+(10-WEEKDAY(A1))))

A1 is first day of month

2
  • Can you explain more?
    – yass
    Apr 12, 2017 at 9:10
  • No need for any explanation - it flat-out doesn't work!
    – robinCTS
    Jul 11, 2018 at 3:37
0

I couldn't get this to work correctly either. I came up with a super complicated (my idiom) way that does appear to work.

A2 = Month
B2 = year
C2 = Day of week (1 - 7, 1 = Sunday)
D2 = "number" (3 = 3rd xday etc.)

SO 2nd Monday would be A2 = current month, B2 = current year, C2 = 2 (for Monday) and D2 = 2 (for 2nd Monday)

F2 = =IF(MONTH(IF((WEEKDAY(VALUE(($A2&"/01/"&$B2))))=$C2,(VALUE(($A2&"/01/"&$B2)))+(($D2*7)-7),IF((WEEKDAY(VALUE(($A2&"/01/"&$B2))))<$C2,((VALUE(($A2&"/01/"&$B2)))+($C2-(WEEKDAY(VALUE(($A2&"/01/"&$B2)))))+(($D2*7)-7)),(VALUE(($A2&"/01/"&$B2)))+($C2-(WEEKDAY(VALUE(($A2&"/01/"&$B2)))))+7+(($D2*7)-7))))=A2,IF((WEEKDAY(VALUE(($A2&"/01/"&$B2))))=$C2,(VALUE(($A2&"/01/"&$B2)))+(($D2*7)-7),IF((WEEKDAY(VALUE(($A2&"/01/"&$B2))))<$C2,((VALUE(($A2&"/01/"&$B2)))+($C2-(WEEKDAY(VALUE(($A2&"/01/"&$B2)))))+(($D2*7)-7)),(VALUE(($A2&"/01/"&$B2)))+($C2-(WEEKDAY(VALUE(($A2&"/01/"&$B2)))))+7+(($D2*7)-7))),"error")

I'm sure there is a much cleaner way to do this (already noticed if I flip one of my IFs around, I can eliminate some replication) plus most of the repeated formulas could be pulled out into separate cells to make the end formula cleaner. But I haven't had a chance to research what other formulas might be available so I'm leaving it as is for now. I also made an even uglier version with error checking and slightly prettier results. But its too complex to post here.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .