certain day of month

T

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
 
R

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))
 
J

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
 
R

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
Click to expand...

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
 
Top