Numbering Trading days per month from a list of Dates

[crazy415]

I have a list of days that the stock market was open. (all of the
closed days have been excluded from the list).

I want to identify what number trading day, within the month, each
date represents.

For example:


Date Trading Day
11/1/2007 1
11/2/2007 2
11/5/2007 3
11/6/2007 4
11/7/2007 5
11/8/2007 6
11/9/2007 7


Is there a combination of functions that will recognize the month
change and identify the trading days until the next month change?

Thanks,

Dano

 

[Gary''s Student]

Say the data starts in A2. In B2 enter 1
In B3 enter:

=IF(MONTH(A3)=MONTH(A2),B2+1,1) and copy down. For example:

Date Trading Day
11/1/2007 1
11/2/2007 2
11/5/2007 3
11/6/2007 4
11/7/2007 5
11/8/2007 6
11/9/2007 7
12/3/2007 1

Now we can lookup using this simple table:

=VLOOKUP(DATEVALUE("11/8/2007"),A2:B100,2) will display 6

 

[Shane Devenshire]

Hi,

Assume your dates start in A2 then in B2 enter the formula

=IF(DAY(A2)>DAY(A1),B1+1,1)

or the shorter but less clear

=IF(DAY(A2)>DAY(A1),B1)+1

and copy it down.

 

[Dana DeLouis]

Another idea...

=NETWORKDAYS($A$1,A1)

and copy down.
The Advantage of using 'Networkdays is that you can have a list of
Holidays the market is closed. One can go to the exchange itself and
get the list of days the market is closed. For example, one would want
to add the Thanksgiving holiday to the list for November.

= = = = =
HTH :>)
Dana DeLouis