	AllExperts > Excel
	Search

Volunteer
Answers to thousands of questions

Home · More Excel Questions · Answer Library

· Encyclopedia ·

Excel - Equivalent Networkdays

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

Add to this Answer Ask a Question

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