Hi Bob

I tried out this formula, but it completly ignores holydays.

My test formulas were
=SUMPRODUCT(INT((B2-WEEKDAY(B2+1-{2;3;4;5;6;7})-A2+8)/7))-SUMPRODUCT(ISNUMBE
R(MATCH(WEEKDAY(L$2:L$14);-{2;3;4;5;6;7};0))*(L$2:L$14=A2)*(L$2:L$14<=B2))

or
=SUMPRODUCT(INT((B2-WEEKDAY(B2+1-M$2:M$7)-A2+8)/7))-SUMPRODUCT(ISNUMBER(MATC
H(WEEKDAY(L$2:L$14);-M$2:M$7;0))*(L$2:L$14=A2)*(L$2:L$14<=B2))
entered as an array formula (While the formula has to read workdays from a
table, I needed cell range instead of array as argument.)

NB! I didn't replace semicolons, which are separators in my windows, with
commas in formulas.

Both formulas return right number of workdays, except they count all
holydays not on free days as workdays. P.e. when I set workdays as
{2;3;4;5;6;7}, i.e. from Monday to Saturday, then January 1st of current
year (1st holiday in year - entered into cell L2) is counted as workday. The
same result is returned by formula
=SUMPRODUCT(INT((B2-WEEKDAY(B2+1-{2;3;4;5;6;7})-A2+8)/7))
or by array formula
=SUMPRODUCT(INT((B2-WEEKDAY(B2+1-M$2:M$7)-A2+8)/7))
, so the rest of formulas is abundant at moment.

It looks like the second sumproduct in both formulas must calculate the
number of holidays falling on workdays between start and end dates (A2 and
B2) - but it doesn't work this way.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Bob Phillips" wrote in message
...
Hi Arvi,

I have this formula


=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1
-{2;3;4;5;6})-MIN(end_date,start_date)+8)/7))-SUMPRODUCT(ISNUMBER(MATCH(WE
EK

DAY(holidays),{2;3;4;5;6},0))*(holidays=MIN(end_d ate,start_date))*(holidays
<=MAX(end_date,start_date)))

where the working days go in the array (twice entered). You can figure
outr
what start_date, end_date and holidays are quite easily yourself.

I also have a UDF somewhere if it must be a UDF, but hopefully this will
work for you.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Arvi Laanemets" wrote in message
...
Hi

NETWORKDAYS counts working days in given date interval, assuming that in
week are 5 working days, and that free days are Saturday and Sunday.
This
assumption is definitely wrong for earlyer periods, and probably also
currently in some countries. Has someone an UDF available, which is an
analog for NETWORKDAYS, but with free days (or workdays) numbers as an
additional array parameter - like holidays.

Thanks in advance.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets