certain day of month

[thomas donino]

Is there a function that will return, for example, the third friday of the
month? Or does one need to create it in vba?
Thank you

 

[Niek Otten]

Look here:

http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

 

[Rick Rothstein]

Assuming A1 contains a date with the correct year and month, use this
formula instead

=DATE(YEAR(A1),MONTH(A1),22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))

The generic version of this formula was posted originally by Peo Sjoblom and
is as follows....

=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

where nth is the number you want 1st, 2nd, 3rd etc thus in my formula 7*3
since you wanted the 3rd Monday and where DoW stands for day of the week
with Sunday starting with 1 and so on and where I put 2 for Monday. If you
want the 2nd Saturday in the month of A1 it would look like

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

 

[Jacob Skaria]

With a date in cell A1 (to pick the year and month) the below formula will do.

=DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6))
(format the formula cell to date format)


If this post helps click Yes

 

[thomas donino]

Wow, great resource, thank you

Look here:

http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Ron Rosenfeld]

Is there a function that will return, for example, the third friday of the
month? Or does one need to create it in vba?
Thank you

Given a date in A1 in the appropriate year and month, then:

=A1-DAY(A1)+22-WEEKDAY(A1-DAY(A1)+2)

will return the third Friday of that month
--ron