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

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(WEEK
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

Hi Bob

Thanks, I'll give it a try! I hope including it in some named value allows
me to avoid too cumbersome formulas on worksheet.

I need it in a yearly calendar, where at bottom the number of workdays for
every month in selected year is calculated. At moment valid holydays list
generation for every year (starting from year 1900) is included (it will be
another task to find out all holidays being in effect in Estonia, and when
they were estabilished or abolished - too much different powers governed
here in last 100 years for my liking - but all I need to do then is to fill
up a table), but now i realized, that it wasn't much years ago, when there
were 6 workdays in a week.

--
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

On Fri, 15 Apr 2005 10:51:20 +0300, "Arvi Laanemets" wrote:

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.

Arvi,

If your weekend days are two, and contiguous, you can modify the NETWORKDAYS
formula by adding/subtracting the appropriate number of days from StartDt and
EndDt.

Here is a UDF, which allows zero to three weekend days which are not
necessarily contiguous. The input arguments should be obvious:

=============================================
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7


Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean

DoHolidays = Not (Holidays Is Nothing)

SD = StartDate: ED = EndDate
If SD ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function
================================
--ron

On Fri, 15 Apr 2005 08:10:39 -0400, Ron Rosenfeld
wrote:

If your weekend days are two, and contiguous, you can modify the NETWORKDAYS
formula by adding/subtracting the appropriate number of days from StartDt and
EndDt.

Just to expand on this concept, you would also have to add/subtract the
appropriate number to the holiday list dates, also.




--ron

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