Hello! I'm running across a problem with calculations -- i'm hoping i can
get a little help here. Some staff are not able to view my NETWORKDAYS
calculations due to excel 2002. It would be useless to have all 50+ staff
perform add-ins. Any thoughts? Also, I used another formula which came up
with more days? Maybe due to 30 or 31 calendar days?

Here's an example of my formulas:
start (b1) = 09/01/09
end (c1) = 09/30/09
=NETWORKDAYS(B1,C1,B45:B56)
This comes out to 21 days

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,B1):INDEX(A:A,C1)),2)<6))--(ISNA(MATCH(ROW(INDIRECT(B1&":"&C1)),B45:B56,0)))
This comes out to 22 days

Help is greatly appreciated!

Try

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,B1):INDEX(A:A,C1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(B1&":"&C1)),B45:B56,0))))

--
__________________________________
HTH

Bob

"HELP JOHNNY PLEASE" <HELP JOHNNY wrote in
message ...
Hello! I'm running across a problem with calculations -- i'm hoping i can
get a little help here. Some staff are not able to view my NETWORKDAYS
calculations due to excel 2002. It would be useless to have all 50+ staff
perform add-ins. Any thoughts? Also, I used another formula which came
up
with more days? Maybe due to 30 or 31 calendar days?

Here's an example of my formulas:
start (b1) = 09/01/09
end (c1) = 09/30/09
=NETWORKDAYS(B1,C1,B45:B56)
This comes out to 21 days

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,B1):INDEX(A:A,C1)),2)<6))--(ISNA(MATCH(ROW(INDIRECT(B1&":"&C1)),B45:B56,0)))
This comes out to 22 days

Help is greatly appreciated!

I have at http://www.cpearson.com/excel/betternetworkdays.aspx a
formula to replace Excel's NETWORKDAYS function with two advantages:
First, it doesn't require the ATP add-in. It works with native Excel
functions. Second, while NETWORKDAYS hard-codes Saturday and Sunday
into the calculation, my formula allows you to specify any one or more
days of the week to exclude from the calculation:

If you don't need to support a list of Holidays, you can use

=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDa te&":"&EndDate))),ExcludeDaysOfWeek,0)),1,0))

where StartDate and EndDate are the obvious end ExcludeDaysOfWeek is a
range or array of up to seven values indicating which days to exclude
(1 = Sunday, 2 = Monday,.... 7 = Saturday).

If you do need to support the Holidays exclusions, use

=IF(OR(StartDate<=0,EndDate<=0,StartDateEndDate,I SNUMBER(StartDate)=FALSE,
ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH (WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),
ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIREC T(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))

The parameters are the same as the first formula with the addition of
a range named Holidays that lists the holidays to exclude.

Both of these are Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 7 Aug 2009 13:53:02 -0700, HELP JOHNNY PLEASE <HELP JOHNNY
wrote:

Hello! I'm running across a problem with calculations -- i'm hoping i can
get a little help here. Some staff are not able to view my NETWORKDAYS
calculations due to excel 2002. It would be useless to have all 50+ staff
perform add-ins. Any thoughts? Also, I used another formula which came up
with more days? Maybe due to 30 or 31 calendar days?

Here's an example of my formulas:
start (b1) = 09/01/09
end (c1) = 09/30/09
=NETWORKDAYS(B1,C1,B45:B56)
This comes out to 21 days

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,B1):INDEX(A:A,C1)),2)<6))--(ISNA(MATCH(ROW(INDIRECT(B1&":"&C1)),B45:B56,0)))
This comes out to 22 days

Help is greatly appreciated!