Weekend days other than Sat Sun

I need one more argument in Networkdays function after Startdate and Enddate,
weekend day or days in form of 1,2,3,4,5,6,7 before holdays

Any solution

Thanx

What do you mean by this? Give some data and expected results.

--

HTH

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


"rkk" <[email protected]> wrote in message
news:[email protected]...
> I need one more argument in Networkdays function after Startdate and
Enddate,
> weekend day or days in form of 1,2,3,4,5,6,7 before holdays
>
> Any solution
>
> Thanx

Sorry, but that's not a option. The function was not written to handle that
problem. If your problem is that your weekend days are not Saturday and
Sunday, but (for example) Friday and Saturday, you can write the formula as

=NETWORKDAYS(A1+1,B1+1,HolidayList)

where A1 and B1 contain the starting and ending dates, and the dates in the
holiday list have all been incremented by 1 as well.

If that's not it, you would need to write a VBA function of your own. If you
can describe your problem more fully, including what the extra argument means,
perhaps we can help.

On Sat, 22 Jan 2005 07:45:02 -0800, rkk <[email protected]> wrote:

>I need one more argument in Networkdays function after Startdate and Enddate,
>weekend day or days in form of 1,2,3,4,5,6,7 before holdays
>
>Any solution
>
>Thanx

Assuming...

A1 contains your start date
B1 contains your end date
C1:C10 contains your holidays

Try the following...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<5),--(1-ISNUMBER(MATCH
(ROW(INDIRECT(A1&":"&B1)),C1:C10,0))))

....which calculates networking days from Monday through Thursday,
excluding the referenced holidays, and where Monday=1, Tuesday=2, etc.
Or, for more flexibility...

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2,3,4})*(1-ISNUMBER(M
ATCH(ROW(INDIRECT(A1&":"&B1)),C1:C10,0))))

Hope this helps!

In article <[email protected]>,
rkk <[email protected]> wrote:

> I need one more argument in Networkdays function after Startdate and Enddate,
> weekend day or days in form of 1,2,3,4,5,6,7 before holdays
>
> Any solution
>
> Thanx

On Sat, 22 Jan 2005 07:45:02 -0800, rkk <[email protected]> wrote:

>I need one more argument in Networkdays function after Startdate and Enddate,
>weekend day or days in form of 1,2,3,4,5,6,7 before holdays
>
>Any solution
>
>Thanx

test
--ron

On Sat, 22 Jan 2005 07:45:02 -0800, rkk <[email protected]> wrote:

>I need one more argument in Networkdays function after Startdate and Enddate,
>weekend day or days in form of 1,2,3,4,5,6,7 before holdays
>
>Any solution
>
>Thanx

Not possible to change the NetWorkdays function.

However, Domenic has given you a worksheet function solution. Here is a VBA
solution that allows you to define up to three weekend days (Sun=1; Sat = 7).
It can be easily modified if more weekend days are required.

To use this, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code into the window that opens.

To use this, enter =NWrkDays(StartDate, EndDate) into some cell. The Holidays
must be represented by a range and the argument is optional, and must be left
blank if you will be defining weekend days. The weekend days are optional and
are entered as numbers. So a formula were the weekend was Sat, Sun and Mon and
where there were no holidays would be:

=NWrkDays(StartDate, EndDate,,1,2,7)


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

Dim c As Range
Dim i As Long, j As Long
Dim Count As Long

For i = StartDate To EndDate
Count = Count + 1
Select Case Weekday(i)
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If Not Holidays Is Nothing Then
For Each c In Holidays
If i = c.Value Then
Count = Count - 1
Exit For
End If
Next c
End If
End Select
Next i

NWrkDays = Count
End Function
=============================
--ron

Hi, Ron:

I'm always "fussing" about the speed of VBA code since it's almost always
slower than built-in functions.

I wonder if a couple of changes in the code would speed it up.

The first (probably most important) is to read the holiday list into an array
and search that rather than accessing the worksheet multiple times. Reading
from or writing to a worksheet always creates a big bottleneck. The other is
to eliminate multiple calls to Weekday.

Using Charles Williams FastExcel2 to time the variations, if there are only
two weekend days (Sat and Sun), a 2-year time span (1/1/03-12/31/05) and a
Holiday list of 190 dates, the times are as follows:

NetWorkDays 1 msec
Your code 565
My code 41

For a shorter time span, 7/22/04 to 8/15/04, and a holiday list containing
2004 dates only (10 holidays).

NetWorkDays 0.18 msec
Your code 0.85
My code 0.46

Both VBA routines are real slouches compared to NETWORKDAYS, but if you need
more than 2 weekend days, the alternatives are limited.

BTW, your code and mine give the same results, identical to NETWORKDAYS.

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

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim DoHolidays As Boolean

If Not Holidays Is Nothing Then
H = Holidays.Value2
DoHolidays = True
End If

w = Weekday(StartDate - 1)
For i = StartDate To EndDate
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, H, 0)) Then Count = Count - 1
End If
End Select
Next i
NWrkDays = Count
End Function

On Sat, 22 Jan 2005 15:44:33 -0600, Myrna Larson
<[email protected]> wrote:

>Using Charles Williams FastExcel2 to time the variations, if there are only
>two weekend days (Sat and Sun), a 2-year time span (1/1/03-12/31/05) and a
>Holiday list of 190 dates, the times are as follows:
>
> NetWorkDays 1 msec
> Your code 565
> My code 41
>
>For a shorter time span, 7/22/04 to 8/15/04, and a holiday list containing
>2004 dates only (10 holidays).
>
> NetWorkDays 0.18 msec
> Your code 0.85
> My code 0.46

It seems odd that your code is slower with the shorter time span. But I
suppose that is within the limits of error of measurement.

I was going to try to use ParamArray to read in both the holiday list and the
weekend days. However, I think it is more valuable to have understandable
function arguments in this instance.
--ron

On Sat, 22 Jan 2005 15:44:33 -0600, Myrna Larson
<[email protected]> wrote:

>I'm always "fussing" about the speed of VBA code since it's almost always
>slower than built-in functions.
>
>I wonder if a couple of changes in the code would speed it up.
>
>The first (probably most important) is to read the holiday list into an array
>and search that rather than accessing the worksheet multiple times. Reading
>from or writing to a worksheet always creates a big bottleneck. The other is
>to eliminate multiple calls to Weekday.

All good suggestions. And substantial speedup based on your timing results.

Ordinarily, my preference is to use arrays. But I was a bit tired last night.
I'm going to try something a bit different and I'll post back.


--ron

Hi,

> =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)={1,2,3,4})*(1-ISNUMBER(M
> ATCH(ROW(INDIRECT(A1&":"&B1)),C1:C10,0))))

FWIW, in cases where the holidays range has a lot fewer dates than
ROW(INDIRECT(A1&":"&B1)), this array formula:

{=SUM(INT((B1-WEEKDAY(B1+1-{2;3;4;5;6})-A1+8)/7))-COUNT(MATCH(WEEKDAY(holidays),
{2;3;4;5;6},0)+MATCH(holidays,ROW(INDIRECT(A1&":"&B1)),0))}

As for the previous formula, you can adapt the constant array (twice in the
formula) to include whatever day of the week you whish to count.
Example: {2;4;6} counts only Mon,Wed,Fri

Regards,

Daniel M.

I just did more fiddling and was surprised by these results

1st routine:

Dim Rng as Range
Dim p As Variant 'because D won't be found
Dim D As Long
Dim i As Long

Set Rng = Worksheets("Sheet1").Range("A2:A200")
D = #12/31/2004#
For i = 1 to 1000
p = Application.Match(D, Rng, 0)
Next i

2nd routine:

Dim v As Variant
Dim p As Variant 'because D won't be found
Dim D As Long
Dim i As Long

v = Worksheets("Sheet1").Range("A2:A200").Value2
D = #12/31/2004#
For i = 1 To 1000
p = Application.Match(D, Rng, 0)
Next i

The first code, using MATCH with a worksheet range, took 29 milliseconds.
Searching the same data in a variant array, took 64 milliseconds, slightly
more than twice as long.

I wonder if VBA is copying all of the data in the array back to Excel's
dataspace every time MATCH is called?



On Sun, 23 Jan 2005 07:42:28 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Sat, 22 Jan 2005 15:44:33 -0600, Myrna Larson
><[email protected]> wrote:
>
>>Using Charles Williams FastExcel2 to time the variations, if there are only
>>two weekend days (Sat and Sun), a 2-year time span (1/1/03-12/31/05) and a
>>Holiday list of 190 dates, the times are as follows:
>>
>> NetWorkDays 1 msec
>> Your code 565
>> My code 41
>>
>>For a shorter time span, 7/22/04 to 8/15/04, and a holiday list containing
>>2004 dates only (10 holidays).
>>
>> NetWorkDays 0.18 msec
>> Your code 0.85
>> My code 0.46
>
>It seems odd that your code is slower with the shorter time span. But I
>suppose that is within the limits of error of measurement.
>
>I was going to try to use ParamArray to read in both the holiday list and the
>weekend days. However, I think it is more valuable to have understandable
>function arguments in this instance.
>--ron

On Sun, 23 Jan 2005 16:08:58 -0600, Myrna Larson
<[email protected]> wrote:

>I just did more fiddling and was surprised by these results
>
>1st routine:
>
> Dim Rng as Range
> Dim p As Variant 'because D won't be found
> Dim D As Long
> Dim i As Long
>
> Set Rng = Worksheets("Sheet1").Range("A2:A200")
> D = #12/31/2004#
> For i = 1 to 1000
> p = Application.Match(D, Rng, 0)
> Next i
>
>2nd routine:
>
> Dim v As Variant
> Dim p As Variant 'because D won't be found
> Dim D As Long
> Dim i As Long
>
> v = Worksheets("Sheet1").Range("A2:A200").Value2
> D = #12/31/2004#
> For i = 1 To 1000
> p = Application.Match(D, Rng, 0)
> Next i
>
>The first code, using MATCH with a worksheet range, took 29 milliseconds.
>Searching the same data in a variant array, took 64 milliseconds, slightly
>more than twice as long.
>
>I wonder if VBA is copying all of the data in the array back to Excel's
>dataspace every time MATCH is called?
>
>
>

I've had some adult beverages this evening, and maybe that's why I don't see
where, in your second routine, Rng is defined ???


--ron

I messed up the message. If you try to run what I posted here, you get an
Invalid Procedure Call or Argument message. In the 2nd message, the line was
supposed to read

p = Application.Match(D, v, 0)

I've included a modification of the NWrkDays function that applies this lesson
on speed.

I compared 4 functions: (1) the built-in NETWORKDAYS function, (2) your
original code, (3) my first code, and (4) NWrkDaysR. The dates are 5/15/2004,
7/15/2004, weekend days are 1 and 7, the holiday list has 190 entries. The
times are

NETWORKDAYS 1.07
Your original 31.47
My first code 2.45 'holidays read into a VBA array, then use MATCH
NWrkDaysR 1.37 'use MATCH on the source range

IMO NWrkDaysR compares favorably with the NETWORKDAYS, given its additional
functionality.

In the past I have posted code that was FASTER than NETWORKDAYS. AIR, I wrote
it to handle only Sat and Sun as the weekend days. The reason it was faster
was

(1) calculate the number of full weeks between start date and end date and
multiply that by 5

(2) calculate day-by-day only on the "tail", the days in the final partial
week

(3) subtract holidays between start date and end date via a binary search on
the holiday list instead of MATCH

The trade-off was that the holiday list had to be sorted ascending and it
could not include any holidays that fell on a weekend day.

~~~~~~~~~~~~~~~~~~~~~~~~~

Function NWrkDaysR(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

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim DoHolidays As Boolean

DoHolidays = Not (Holidays Is Nothing)

w = Weekday(StartDate - 1)
For i = StartDate To EndDate
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
NWrkDaysR = Count
End Function

On Sun, 23 Jan 2005 20:29:08 -0600, Myrna Larson
<[email protected]> wrote:

> NETWORKDAYS 1.07
> Your original 31.47
> My first code 2.45 'holidays read into a VBA array, then use MATCH
> NWrkDaysR 1.37 'use MATCH on the source range

Nice.


--ron

On Sun, 23 Jan 2005 20:29:08 -0600, Myrna Larson
<[email protected]> wrote:

> NETWORKDAYS 1.07
> Your original 31.47
> My first code 2.45 'holidays read into a VBA array, then use MATCH
> NWrkDaysR 1.37 'use MATCH on the source range

Nice.


--ron

>
{=SUM(INT((B1-WEEKDAY(B1+1-{2;3;4;5;6})-A1+8)/7))-COUNT(MATCH(WEEKDAY(holidays),
> {2;3;4;5;6},0)+MATCH(holidays,ROW(INDIRECT(A1&":"&B1)),0))}

Better (faster) is this non-array formula (again, it's most appropriate for
cases where one does not have big holiday list and A1 << B1) :

=SUMPRODUCT(INT((B1-WEEKDAY(B1+1-{2;3;4;5;6})-A1+8)/7))-SUMPRODUCT(ISNUMBER(
MATCH(WEEKDAY(holidays),{2;3;4;5;6},0))*(holidays>=A1)*(holidays<=B1))

Regards,

Daniel M.

Except that when I test it today, NWrkDaysR is take about 2.5 times as long as
NETWORKDAYS, not 30% longer. Don't know why...

On Mon, 24 Jan 2005 08:06:00 -0500, Ron Rosenfeld <[email protected]>
wrote:

>On Sun, 23 Jan 2005 20:29:08 -0600, Myrna Larson
><[email protected]> wrote:
>
>> NETWORKDAYS 1.07
>> Your original 31.47
>> My first code 2.45 'holidays read into a VBA array, then use MATCH
>> NWrkDaysR 1.37 'use MATCH on the source range
>
>Nice.
>
>
>--ron