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

Look here:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"thomas donino" <(E-Mail Removed)> wrote in message
news:50DBAF1F-4A90-4FA4-86DF-(E-Mail Removed)...
> 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

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

--
Rick (MVP - Excel)


"thomas donino" <(E-Mail Removed)> wrote in message
news:50DBAF1F-4A90-4FA4-86DF-(E-Mail Removed)...
> 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

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
---------------
Jacob Skaria


"thomas donino" wrote:

> 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

Wow, great resource, thank you

"Niek Otten" wrote:

> Look here:
>
> http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "thomas donino" <(E-Mail Removed)> wrote in message
> news:50DBAF1F-4A90-4FA4-86DF-(E-Mail Removed)...
> > 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
>
>

On Tue, 11 Aug 2009 08:14:02 -0700, thomas donino
<(E-Mail Removed)> wrote:

>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