Hi
Some time ago I created an equivalent to WORKDAYS excel function
(EnchWorkaysN()), which allows you to count workdays between 2 dates. To
have it work, you have to copy another 3 functions into your VBA module too.
=ENCHWORKDAYS(Date1,Date2,[Holidays],[Weekends],[Weekstart])
, where
Date1 and Date2 are start and end dates;
Holidays is a holiday list (a range reference or an array or a single
date)
Weekends is a weekend days (1-7) list (a range reference or an array or
single number)
Weekstart redefines 1st day of week for weekends count
(1 - sunday, 2 - monday, etc.)
You need a similar equivalent of NETWORKDAYS function for your task. Maybe
you can use my code as start point.
-------------------------------------------
Option Base 1
Function SelectionSort(TempArray As Variant)
Dim MaxVal As Variant
Dim MaxIndex As Integer
Dim i, j As Integer
' The function sorts all entries in 1-dimensional array,
' it's a function provided in Microsoft KB article 133135
' Step through the elements in the array starting with the
' last element in the array.
For i = UBound(TempArray) To 1 Step -1
' Set MaxVal to the element in the array and save the
' index of this element as MaxIndex.
MaxVal = TempArray(i)
MaxIndex = i
' Loop through the remaining elements to see if any is
' larger than MaxVal. If it is then set this element
' to be the new MaxVal.
For j = 1 To i
If TempArray(j) > MaxVal Then
MaxVal = TempArray(j)
MaxIndex = j
End If
Next j
' If the index of the largest element is not i, then
' exchange this element with element i.
If MaxIndex < i Then
TempArray(MaxIndex) = TempArray(i)
TempArray(i) = MaxVal
End If
Next i
End Function
Function SelectionUnique(TempArray As Variant, Optional AllowZeros As
Boolean = True)
Dim MaxVal, TempArray2() As Variant
Dim MaxIndex As Integer
Dim i, j As Integer
' The function is meant to work with ordered arrays
' and removes all double entries and Null values
' (Except when the is the only value, and it is Null).
' Optional argument determines, how 0 values are processed
' Initialize
j = 1
ReDim TempArray2(1 To j) As Variant
TempArray2(1) = Null
' Step through the elements in the array starting with the
' first element in the array.
For i = 1 To UBound(TempArray) Step 1
If IsNull(TempArray(i)) Or _
IsEmpty(TempArray(i)) Or _
(TempArray(i) = 0 And AllowZeros = False) Then
Else
' Redim TempArray2 and add an element
ReDim Preserve TempArray2(1 To j) As Variant
TempArray2(j) = TempArray(i)
j = j + 1
' Set CurrVal to the element in the array
currval = TempArray(i)
' Cycle through next elements until value changes
k = 0
If i < UBound(TempArray) Then
Do While TempArray(i + k + 1) = currval
k = k + 1
If i + k > UBound(TempArray) Then Exit Do
Loop
End If
i = Application.WorksheetFunction.Max(i, i + k - 1)
End If
Next i
' Write the passed array over
TempArray = TempArray2
End Function
Function SelectionToInteger(TempArray As Variant)
Dim i As Integer
' The function cuts off decimal part from all non-empty elements of
array
' Step through the elements in the array starting with the
' first element in the array.
For i = 1 To UBound(TempArray) Step 1
If IsNull(TempArray(i)) Then
Else
' Replace array element with it's integer value
TempArray(i) = Int(TempArray(i))
End If
Next i
End Function
' NB! The function starts here!!!
Public Function EnchWorkdaysN(StartDate As Date, _
EndDate As Date, _
Optional Holidays As Variant = Nothing, _
Optional Weekends As Variant = Nothing, _
Optional WeekStart As Integer = 1)
Dim arrayH As Variant, arrayW As Variant
Dim di As Date, dn As Date, dx As Date
' The result doesn't depend on order of values of first 2 parameters.
' When parameter Holidays is omitted, or Null, or not a positive numeric
(date) value,
' or not an array or cell range with numeric values, then no holidays
' are left out from day's count.
' When parameter Weekends is omitted, or Null, or not a numeric value
' or not an array or cell range with at least one numeric value between
' then 1 and 7 (Saturday and Sunday) are set for Weekend default walues,
' and according weekdays are left out from day's count.
' No weekends are left out from day's count (7-workday week) only then,
' when fourth parameter is set to FALSE.
' The parameter WeekStart determines, how are determined weekends in 4th
parameter
' Allowed values for parameter WeekStart are integers 1 to 7.
' The number 1 indicates Sunday as 1st day of week,
' the number 2 indicates Monday as first day of week, etc.
' When the parameter WeekStart is not between 1 and 7, then WeekStart =
(Abs(WeekStart) Mod 7)+1
' Initialize ArrayH
If TypeName(Holidays) = "Variant()" Then
ReDim arrayH(1 To UBound(Holidays)) As Variant
For i = 1 To UBound(Holidays)
arrayH(i) = IIf(VarType(Holidays(i, 1)) > 0 And
VarType(Holidays(i, 1)) < 8, Holidays(i, 1), Null)
arrayH(i) = IIf(arrayH(i) < 0, Null, arrayH(i))
Next i
ElseIf (VarType(Holidays) >= 8192 And VarType(Holidays) <= 8199) Or _
VarType(Holidays) = 8204 Then
ReDim arrayH(1 To UBound(Holidays.Value)) As Variant
For i = 1 To UBound(Holidays.Value)
arrayH(i) = IIf(VarType(Holidays(i)) > 0 And
VarType(Holidays(i)) < 8, Holidays(i), Null)
arrayH(i) = IIf(arrayH(i) < 0, Null, arrayH(i))
Next i
ElseIf VarType(Holidays) < 8 Then
ReDim arrayH(1) As Variant
arrayH(1) = Holidays
arrayH(1) = IIf(arrayH(1) < 0, Null, arrayH(1))
Else
ReDim arrayH(1) As Variant
arrayH(1) = Null
End If
' Sort arrayH
SelectionSort arrayH
' Replace non-integer values with integers
SelectionToInteger arrayH
' Remove double entries and empty elements
SelectionUnique arrayH
' Initialize arrayW
If VarType(Weekends) <> 11 Then
If TypeName(Weekends) = "Nothing" Then
ReDim arrayW(1 To 2) As Variant
arrayW(1) = 1
arrayW(2) = 7
ElseIf TypeName(Weekends) = "Variant()" Then
ReDim arrayW(1 To UBound(Weekends)) As Variant
For i = 1 To UBound(Weekends)
If UBound(Weekends) = 1 Then
arrayW(i) = IIf(VarType(Weekends(i)) > 0 And
VarType(Weekends(i)) < 8, ((Abs(Weekends(i)) + 12 + WeekStart) Mod 7) + 1,
Null)
Else
arrayW(i) = IIf(VarType(Weekends(i, 1)) > 0 And
VarType(Weekends(i, 1)) < 8, ((Abs(Weekends(i, 1)) + 12 + WeekStart) Mod 7)
+ 1, Null)
End If
arrayW(i) = IIf(arrayW(i) < 1 Or arrayW(i) >= 8, Null,
arrayW(i))
Next i
ElseIf VarType(Weekends) >= 8192 And VarType(Weekends) <= 8199 Or _
VarType(Weekends) = 8204 Then
ReDim arrayW(1 To UBound(Weekends.Value)) As Variant
For i = 1 To UBound(Weekends.Value)
arrayW(i) = IIf(VarType(Weekends(i)) > 0 And
VarType(Weekends(i)) < 8, ((Abs(Weekends(i)) + 12 + WeekStart) Mod 7) + 1,
Null)
arrayW(i) = IIf(arrayW(i) < 1 Or arrayW(i) >= 8, Null,
arrayW(i))
Next i
ElseIf (Int(Weekends) >= 1 And Int(Weekends) < 8) Then
ReDim arrayW(1) As Variant
arrayW(1) = ((Abs(Weekends) + 12 + WeekStart) Mod 7) + 1
arrayW(1) = IIf(arrayW(1) < 1 Or arrayW(1) >= 8, Null,
arrayW(1))
Else
ReDim arrayW(1 To 2) As Variant
arrayW(1) = 1
arrayW(2) = 7
End If
' Sort arrayW
SelectionSort arrayW
' Replace non-integer values with integers
SelectionToInteger arrayW
' Remove double entries and empty elements
SelectionUnique arrayW, False
Else
' Set 1st element to 0 for 7-workday week
ReDim arrayW(1) As Variant
arrayW(1) = IIf(Weekends = False, 0, Null)
End If
' When empty array, insert default values
If arrayW(1) = Null Then
ReDim arrayW(1 To 2, 1) As Variant
arrayW(1) = 1
arrayW(2) = 7
End If
' Calculate the number of workdays in date interval determined by
StartDay and EndDay
EnchWorkdaysN = 0
di = Application.WorksheetFunction.Min(StartDate, EndDate)
dn = Application.WorksheetFunction.Max(StartDate, EndDate)
dx = di
Do While dx <= dn
x = False
i = 1
Do While x = False And i <= UBound(arrayH) And TypeName(arrayH(1))
<> "Null"
x = (dx = arrayH(i))
i = i + 1
Loop
i = 1
Do While x = False And i <= UBound(arrayW) And arrayW(1) <> 0
x = (Weekday(dx) = arrayW(i))
i = i + 1
Loop
If Not (x) Then EnchWorkdaysN = EnchWorkdaysN + 1
dx = dx + 1
Loop
End Function
