Expert: Chad Welch - 10/4/2009

Question
Hi Chad,

I'm rying to recreate networkdays. I got a code taken from internet but I have trouble including the holidays.

Appreciate your help. Thank you!

Sanch

This is the code:
Public Function MyNetWorkdays(ByVal dteStart As Date, ByVal dteEnd As Date, dteHol As Range) As Integer

Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer

'intGrossDays = DateDiff("d", dteStart, dteEnd)
intGrossDays = DateDiff("d", dteStart, dteEnd, vbMonday)
MyNetWorkdays = 0

For i = 0 To intGrossDays
  dteCurrDate = dteStart + i
  If Weekday(dteCurrDate, vbMonday) < 6 Then
     'If FindItemInArray(dteHol, dteCurrDate) Then
        MyNetWorkdays = MyNetWorkdays + 1
     'End If
  End If
Next i

End Function  

Answer
Sanch,
A couple of your lines have been commented out. As it stands now, your code will return the same result as NETWORKDAYS. To work properly, uncomment the 2 lines in your For loop:
   If FindItemInArray(dteHol, dteCurrDate) Then
   End If
If you try the function now, you will probably get an error, because it is looking for another function named "FindItemInArray." If you can go back to the page where you got this code look for a function named that.  If you don't remember where you found this code, the following should work. I just wrote it and haven't tested it completely, but it should workL

Private Function FindItemInArray(RangeToSearch As Range, DateToFind As Date) As Boolean

Dim cel As Range

FindItemInArray = True

For Each cel In RangeToSearch
   If cel = DateToFind Then
       FindItemInArray = False
       GoTo ExitEarly
   End If
Next cel

ExitEarly:
Exit Function
End Function


Best wishes,
Chad Welch