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

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.

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

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:

>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

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

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