NETWORKDAYS (Including Saturday)

Hi Folks

I know that NETWORKDAYS is used to count the number of days between a range excluding Saturday & Sunday, but what if I wanted to exclude only Sunday?

Cheers.

Chard

Thanks mate!

Here's another way.....

=B1-A1+1-INT((8-WEEKDAY(B1)+B1-A1)/7)

Originally Posted by NBVC

Try:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&B1)),"dddd")<>"Sunday"))

Where A1 and B1 house the From/To dates

That's an interesting formula. I tried to disect it to figure out how it works, but I failed. Any chance you could give a brief explanation on what is happening there?

Assuming A1 is today's date and B1 is 10 days later then this part

INDIRECT(A1&":"&B1)

will give

39225:39235

(because Today's date serial is 39225)

then ROW(39225:39235)

will give the array

{39225;39226;39227;39228;39229;39230;39231;39232;39233;39234;39235}

i.e. each of the date serials for every date in the range

TEXT function then converts these to

={"Wednesday";"Thursday";"Friday";"Saturday";"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}

and SUMPRODUCT counts those that aren't Sundays, giving 9 for my example.

You could also use the WEEKDAY function, i.e.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<>1))

although both iof these formulas are more expensive, computationally, than my alternative above, which I can even simplify to:

=B1-A1-INT((1-WEEKDAY(B1)+B1-A1)/7)

Note: unless your using Excel 2007 the SUMPRODUCT formulas can work with dates beyond 5th June 2079 because ROW function fails when the date serial goes beyond maximum row number, 65536

Both NBVC and DaddyLongLegs:

Thanks for the explanation and the links. I understood each part of the formula individually, but I failed to compute the dates as their serial numbers. For that reason, INDIRECT(4/15/07:4/21/07) did NOT make much sense to me.

Now, it is much clearer.