Excel – How to identify first , second etc Monday or other day of week for a given month in excel

excel

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

Best Solution

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

Related Question