It's good to hear from you.

So you remember! It's been a while, at least 3 years since I was involved with
the ngs!

I hope that helps.

Yes, it certainly does -- Thanks!!! I see now where my thinking was going
astray: misplaced parens:

I was breaking it down into WEEKDAY(A1) - 2, WEEKDAY(A1) - 3, etc, which
doesn't "roll over" from 1 to 7, so when A1 is a Monday, my incorrect version
would return the series 0,-1,-2,-3,-4, instead of 7,6,5,4,3.

Thanks for posting the original formula and the explanation! I hope it helps
somebody other than just me.

Myrna Larson


On Fri, 26 Oct 2007 08:35:49 -0400, "Ron Coderre"
wrote:

Hi, Myrna! It's good to hear from you.

For ease of explanation, let's use this variation
of the formula (with Mon as DAY 1):
=SUM(INT((WEEKDAY(A1-{1,2,3,4,5},2)+B1-A1)/7))

and a 1-day range: 01-JAN-2007 through 01-JAN-2007

The EndDate - StartDate difference, in this case, is zero.

Now, we'll deal with the WEEKDAY section.
Since 01-JAN-2007 is a Monday, its weekday is: 1
Subtracting 1 day from 01-JAN-2007 makes it a Sunday (weekday 7).
Subtracting 2 days from 01-JAN-2007 makes it a Saturday (weekday 6).
etc.returning an array of:
{7,6,5,4,3}

When that array is added to the EndDate - StartDate difference
of zero, the result is that same array:
{7,6,5,4,3}

Dividing each array element by 7 returns:
{1, 0.857, 0.714, 0.571, 0.428}

Truncating each element with the INT function results in:
{1, 0, 0, 0, 0}

That array represents the number of times Mon, Tue, Wed, Thu, and Fri
occur in the StartDate thru EndDate period.

If our range was 02-JAN-2007 (Tue) through 02-JAN-2007
The final array would be:
{0, 1, 0, 0, 0}

If our range was 01-JAN-2007 (Mon) through 08-JAN-2007 (Mon)
The final array would be:
{2, 1, 1, 1, 1}