Date functions

D

Dale

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
 
M

Martin P

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.
 
R

Ron Rosenfeld

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

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
 
M

Martin P

Correction.

For my method, another column is needed:

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