AllExperts > Excel 
Search      
Excel
Volunteer
Answers to thousands of questions
 Home · More Excel Questions · Answer Library  · Encyclopedia ·
More Excel Answers
Question Library

Ask a question about Excel
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Chad Welch
Expertise
I have a lot of experience with VBA; Sub procedures, functions etc. I am also very familiar with array formulas.

Experience
I've worked with Excel for several years. I've been answering questions on various groups and lists for a while.

Organizations
Brainbench MVP for MS Excel.

 
   

You are here:  Experts > Computing/Technology > Microsoft Software > Excel > Equivalent Networkdays

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
User Agreement | Privacy Policy | Kids' Privacy Policy | Help
Copyright  © 2008 About, Inc. AllExperts, AllExperts.com, and About.com are registered trademarks of About, Inc. All rights reserved.