Counting the number of days.

I'll sacrifice my pride and give you an example of one, very convuluted way. Please, please check back if I happen to be the first one to respond to this as I'm sure there will be a much better way provided. This just caught my eye and I started working on it.

Start date is in A1 and End date is in A2.

Put the numeric value of your start day in say a4
=WEEKDAY(A1)

Put the numeric value of your end day in a5
=WEEKDAY(A2)

Put the total days in the period in a6
=+A2-A1 (format general)

List your days, starting with Sunday in A8..A14
Next the days in B8..B14 put 1 through 7.

Now in put the following in C8 and copy it through C14
=IF(AND(B8>=$A$4,B8<=$A$5),ROUNDUP($A$6/7,0),ROUNDDOWN($A$6/7,0))

This survived a quick test of your data, but you will want to double check the first few time you use it.

No sit back and wait for someone to let us all know about some =countthenumberofdaysfunction that must be out there.

Never mind - doesn't work

Sorry. It returns zero days when entering 01/01/01 through 01/08/01. Also doesn't work if you start and stop on the same day.

Re: Never mind - doesn't work

I can't think of a quick fix for the less than 7 days, but this revised formula should fix if the period starts and ends on the same day of the week.

=IF(AND(B8=$A$4,B8=$A$5),ROUNDUP($A$6/7+1,0),IF(B8>AND($A$4,B8<$A$5),ROUNDUP($A$6/7,0),ROUNDDOWN($A$6/7,0)))

I assume that the start date is in A2, and the
ending date is in B2.

Have A4 to A10 be 1,2,3,4,5,6, and 7.
Now B4 = ROUNDDOWN((DATEDIF($A$2,$B$2,"d")+1)/7,0)+IF(WEEKDAY($A$2)>A4,0,1)-IF(WEEKDAY($B$2)>=A4,0,1)
and copy B4 down to B5:B10.

This will work as long as B4>A4.

Good luck!

1. Enter 1/1/01 into cell A1
2. Enter 3/31/01 into cell A2
3. Enter {"Sundays";"Mondays";"Tuesdays";"Wednesdays";"Thursdays";"Fridays";"Saturdays"}
into cells A4:A10
4. Enter {1;2;3;4;5;6;7} into cells B4:B10
5. Enter the array formula,
{=SUM((WEEKDAY(TREND($A$1:$A$2,{0;1}*($A$2-$A$1),ROW(INDIRECT("1:"&$A$2-$A$1+1))-1))=$B4)+0)}
into cell C4 and copy down to cell C10

Note: Array formula must be entered using Control+Shift+Enter

Leap Year Snag

Tim, your formula produces {8;8;7;7;8;8;8} for
the dates 1/1/04 and 3/1/04 instead of
{9;9;8;8;9;9;9}.

Between all the suggestions I was able to get the right formula.

Thanks everyone especially Mark

Mark I had to modify yours slight for when the start and end dates were the same but I got it to work. Thanks again.

Notable limitations...

Dwight, there is a another limitation to my formula,
{=SUM((WEEKDAY(TREND($A$1:$A$2,{0;1}*($A$2-$A$1),ROW(INDIRECT("1:"&$A$2-$A$1+1))-1))=$B4)+0)}.
It will not handle case where the difference between
the 2 dates is greater than 65535. This limitation
is a result of its reliance on the ROW() function
to create an array constant. Excel only supports
65536 rows. My formula relys on "brute force" to
provide a solution and; therefore, is rather
inelegant. Now that I've "trashed" my own formula
let me defend it from a different angle. The
true value of this formula is as a validation of
other approaches. I really think Tim was on the
right track, and all his formulation needed was
a few tweaks.

Re: Leap Year Snag

Aha! It's not just a leap year snag...
=ROUNDDOWN(($B$2-$A$2+1)/7,0)+IF(WEEKDAY($A$2)<=WEEKDAY($B$2),IF(AND(A4>=WEEKDAY($A$2),A4<=WEEKDAY($B$2)),1,0),IF(OR(A4>=WEEKDAY($A$2),A4<=WEEKDAY($B$2)),1,0))

I've run a battery of tests, and this works!
The only real advantage that this has over the array formula is that it's not constrained by the integer limit of 65535.

IMO, the *real* advantage of yours..., but there's still a problem

Tim, I believe that yours will outperform mine because
it uses less computing resouces; however, there
still seems to be a problem...

Using 1/1/2001 and 12/31/2079 I get...

{4122;4122;4122;4122;4122;4122;4122}

...while you get...

{4123;4123;4123;4123;4123;4123;4123}

="12/31/20079"-"1/1/2001"+1 produces 28854

{=SUM({4122;4122;4122;4122;4122;4122;4122})} produces 28854
{=SUM({4123;4123;4123;4123;4123;4123;4123})} produces 28861

Sorry, for the bad news! I'd really like to see
you pull this one off.

Understood

Understood.

That should just about do it...

I found my mistake, I think. The formula checks to see which case applies: is A2 earlier in the week than B2, or "around the horn" of the weekend.

If A2 is one day benind B2, then it should be part of the first case (because no day of the week should have any more days). Hence the "+1" in WEEKDAY($B$2)+1.

=ROUNDDOWN(($B$2-$A$2+1)/7,0)+IF(WEEKDAY($A$2)<=WEEKDAY($B$2)+1,IF(AND(A5>=WEEKDAY($A$2),A5<=WEEKDAY($B$2)),1,0),IF(OR(A5>=WEEKDAY($A$2),A5<=WEEKDAY($B$2)),1,0))

Getting closer...

Using 1/1/2001 and 12/31/2001...

mine... {52;53;52;52;52;52;52}
yours.. {53;52;52;52;52;52;52}

...the total number of days match!

Re: Getting closer...

I'm getting {52;53;52;52;52;52;52}
in my Excel sheet. I think I copied the
formula from row 5 instead of the one on
the top (from row 4). So this works:

=ROUNDDOWN(($B$2-$A$2+1)/7,0)+IF(WEEKDAY($A$2)<=WEEKDAY($B$2)+1,IF(AND(A4>=WEEKDAY($A$2),A4<=WEEKDAY($B$2)),1,0),IF(OR(A4>=WEEKDAY($A$2),A4<=WEEKDAY($B$2)),1,0))

Looking Good! Congratulations

Bravo - But what will you do for an encore?

Cold Fusion... : )

Cold Fusion (Still learning) :-)

I'm using this:
WHERE trayname IN (#ValueList(GetCookies.trayname)#);

and i'm getting an error that says this...
Error Diagnostic Information

An error occurred while evaluating the expression:

#ValueList(GetCookies.trayname)#

Error near line 26, column 25.
--------------------------------------------------------------------------------

Parameter 1 of function ValueList which is now "GetCookies.trayname" must be pointing to a valid column name

The error occurred while processing an element with a general identifier of (#ValueList(GetCookies.trayname)#), occupying document position (26:24) to (26:55).

Can anyone help me please?