ExcelBanter - View Single Post

Ron Rosenfeld

On 14 Nov 2004 16:59:38 -0800, (DCondie) wrote:

Working in Excel 2003

I am creating a database that lists the daily prices of stocks for the
days that I hold each stock over a twenty-year period. For each
stock, I have the price for each day that I held the stock.

I need to place the price for each day that the trade is open in a
column on the row adjacent to the first column which indicates the
trade day.

Can you provide a procedure that would eliminate Saturday, Sunday and
holidays from the date sequence over a twenty-year period staring in
1/1/83 so that the date column would only show sequentially the
available trade dates over the twenty year period?

Thank you.

Doug Condie

The WORKDAY function will do that, but you will need to generate a list of all
the market holidays. For example, with your list of holidays in a range named
"holidays":

A1: 3 Jan 1994 (First market day in 1994)
A2: =WORKDAY(A1,1,holidays)

And copy/drag down.

--ron