Excel/Equivalent Networkdays
Expert: Chad Welch - 10/4/2009
QuestionHi 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
AnswerSanch,
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
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your
Excel question answered in this area then please ask an Excel question here