Date functions-first Friday or even the last Tuesday?

Hi, fairly new to spreadsheets but I keep trying. Any idea how to insert
the first Friday of a month into columns? I know the (today) and (date)
functions but what if I wanted a specific day of the month. Like the first
Friday or even the last Tuesday? Thanks
Dale

Take a look at

http://cpearson.com/excel/DateTimeWS.htm#FirstWeekday

and

http://cpearson.com/excel/DateTimeVBA.htm#Ndow

In article <[email protected]>,
"Dale" <[email protected]> wrote:

> Hi, fairly new to spreadsheets but I keep trying. Any idea how to insert
> the first Friday of a month into columns? I know the (today) and (date)
> functions but what if I wanted a specific day of the month. Like the first
> Friday or even the last Tuesday? Thanks

Start with the first day of the month in column A. Let's say you have
=date(2005,4,1) in cell A18. In cell B18 enter =weekday(A18). In cell C18
enter =6-B18.
In cell D18 enter =A18+B18, which gives you the first Friday of April 2005.

The procedure for the last Tuesday of every month is similar, but take the
last day of the month, and subtract from 3 instead of 6.

"Dale" wrote:

> Hi, fairly new to spreadsheets but I keep trying. Any idea how to insert
> the first Friday of a month into columns? I know the (today) and (date)
> functions but what if I wanted a specific day of the month. Like the first
> Friday or even the last Tuesday? Thanks
> Dale
>
>
>

On Sat, 09 Apr 2005 18:29:19 GMT, "Dale" <[email protected]> wrote:

>Hi, fairly new to spreadsheets but I keep trying. Any idea how to insert
>the first Friday of a month into columns? I know the (today) and (date)
>functions but what if I wanted a specific day of the month. Like the first
>Friday or even the last Tuesday? Thanks
>Dale
>

I'm not sure exactly what you want to set up.

The first Friday of any particular month is given by the formula:

=DATE(yr,month,8)-WEEKDAY(DATE(yr,month,9))

To compute subsequent first Fridays, assuming the above formula is in B1, use
the formula:

=B1+28+7*(MONTH(B1+28)=MONTH(B1))

If you want the 2nd, 3rd or 4th Friday, add 7,14 or 21 to the formula in B1,
and use this formula for subsequent months:

=B1+28+7*(DAY(B1-DAY(B1)+35)<WEEKDAY(B1-DAY(B1)-5))

The above are adapted from formulas by Daniel M.
--ron

Thx, just what I was looking for.
Dale


"Dale" <[email protected]> wrote in message
news:[email protected]...
> Hi, fairly new to spreadsheets but I keep trying. Any idea how to insert
> the first Friday of a month into columns? I know the (today) and (date)
> functions but what if I wanted a specific day of the month. Like the
> first Friday or even the last Tuesday? Thanks
> Dale
>

Correction.

For my method, another column is needed:

In E18: =D18+7*(MONTH(D18)<>MONTH(A18))

"Martin P" wrote:

> Start with the first day of the month in column A. Let's say you have
> =date(2005,4,1) in cell A18. In cell B18 enter =weekday(A18). In cell C18
> enter =6-B18.
> In cell D18 enter =A18+B18, which gives you the first Friday of April 2005.
>
> The procedure for the last Tuesday of every month is similar, but take the
> last day of the month, and subtract from 3 instead of 6.
>
> "Dale" wrote:
>
> > Hi, fairly new to spreadsheets but I keep trying. Any idea how to insert
> > the first Friday of a month into columns? I know the (today) and (date)
> > functions but what if I wanted a specific day of the month. Like the first
> > Friday or even the last Tuesday? Thanks
> > Dale
> >
> >
> >