What would be the best way to create a formula that will return a list
of consecutive #s when the list of dates are missing weekends and
holidays? What I want the formula to do is return the # in column B
understanding when it switches to a new month so that it resets back to
1. I'm doing this on dates back to 1900 which is why I need a formula
that can speed up the process. Any help would be appreciated.


1/3/2006 1
1/4/2006 2
1/5/2006 3
1/6/2006 4
1/9/2006 5
1/10/2006 6
1/11/2006 7
1/12/2006 8
1/13/2006 9
1/17/2006 10
1/18/2006 11
1/19/2006 12
1/20/2006 13
1/23/2006 14
1/24/2006 15
1/25/2006 16
1/26/2006 17
1/27/2006 18
1/30/2006 19
1/31/2006 20
2/1/2006 1
2/2/2006 2
2/3/2006 3
2/6/2006 4
2/7/2006 5
2/8/2006 6
2/9/2006 7
2/10/2006 8
2/13/2006 9
2/14/2006 10
2/15/2006 11

Hi!

Assume your dates are in the range A1:A31

In B1 enter 1.

Enter this formula in B2 and copy down as needed:

=IF(MONTH(A2)=MONTH(A1),B1+1,1)

Biff

"zee" wrote in message
oups.com...
What would be the best way to create a formula that will return a list
of consecutive #s when the list of dates are missing weekends and
holidays? What I want the formula to do is return the # in column B
understanding when it switches to a new month so that it resets back to
1. I'm doing this on dates back to 1900 which is why I need a formula
that can speed up the process. Any help would be appreciated.


1/3/2006 1
1/4/2006 2
1/5/2006 3
1/6/2006 4
1/9/2006 5
1/10/2006 6
1/11/2006 7
1/12/2006 8
1/13/2006 9
1/17/2006 10
1/18/2006 11
1/19/2006 12
1/20/2006 13
1/23/2006 14
1/24/2006 15
1/25/2006 16
1/26/2006 17
1/27/2006 18
1/30/2006 19
1/31/2006 20
2/1/2006 1
2/2/2006 2
2/3/2006 3
2/6/2006 4
2/7/2006 5
2/8/2006 6
2/9/2006 7
2/10/2006 8
2/13/2006 9
2/14/2006 10
2/15/2006 11

Wow! That works perfectly. Thanks a million.
Biff wrote:
Hi!

Assume your dates are in the range A1:A31

In B1 enter 1.

Enter this formula in B2 and copy down as needed:

=IF(MONTH(A2)=MONTH(A1),B1+1,1)

Biff

You're welcome. Thanks for the feedback!

Biff

"Number_Cruncher" wrote in message
oups.com...
Wow! That works perfectly. Thanks a million.
Biff wrote:
Hi!

Assume your dates are in the range A1:A31

In B1 enter 1.

Enter this formula in B2 and copy down as needed:

=IF(MONTH(A2)=MONTH(A1),B1+1,1)

Biff