How to identify first , second etc Monday or other

Question

I was looking in internet, but the formulas look so complicated. Any clever suggestions? /i.e for example I need a formula that identify which date is the first Monday in August 2014, similar to be used- for the second Monday, etc/ Thank you

Answer 1

Here's a shorter answer:

=WORKDAY.INTL(A1-1,1,"0111111")

where A1 is the cell that contains your date.

The second parameter is the nth day of the week that you want. In this case, 1 = the 1st, 2 = 2nd etc. Depending on the month, you can get up to a 5th occurrence of the same day.

Bonus: you can count the nth day of the week that you want beyond the month!

The third parameter is bitwise. There are seven binary digits, put a "0" on the day that your looking for and then one for the rest. The first digit is Monday up to the seventh digit for Sunday. In this example "0111111" gives you mondays.

Sample 2:

=WORKDAY.INTL(A1-1,3,"1101111")

Here, you're looking for the 3rd Wednesday after the date in cell A1.

More on WORKDAY.INTL here.

Answer 2

Generically, you can find the n-th of an x day of a given M and Y with this formula

=DATE(Y,M,(n*7)+1)-WEEKDAY(DATE(Y,M,8-x), 2)

where x is a number representing the day of the week from 1 = Sunday through to 7 = Saturday

So from that 1st Monday in August 2014 is found by this formula

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

If you want the last Monday in any given month you can find the first Monday of the next month and subtract 7

See my article here for more

Answer 3

Try the below code

=A2+MOD(8-WEEKDAY(A2,2),7)

Where your A2 cell will contain 01/Month/year like 01/march/2014 but make sure it must have to start with 01.