Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Thanked 0 Times in 0 Posts

    Lightbulb Networkdays (Excel 2003) UDF

    I need to use NETWORKDAYS functions in Excell 2003 ( VBA,) without using Excel Tools Add-Ins menu! If anyone can help me with a UDF -function!
    I found something here but I need and with Saturday to be include in not working day and in Holydays to be able to use a column of date.
    Please don`t ask me why I`m not using Excel Tools Add-Ins menu. It company police.


  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,455 Times in 1,324 Posts

    Here's a modified version of the above code which should meet your needs.
    Option Explicit
    Public Function SixDayWorkWeekBetween(rngInStart As Range, rngInEnd As Range, _
                   rngHolidays As Range, Optional vInclusive As Variant) As Long
    ' Arguments are
    '  Start Date cell,
    '  End Date cell,
    '  range of Holidays in valid Excel date format (preferablly a named range!),
    '  True/False is the calculation Inclusive of all dates or exclusive.
    '     Ex 12/31/2012 through 1/8/2013 would return 6 workdays inclusive or
    '                                                 5 workdays exclusive
    '           with 1/1/2013 in the holidays list and a 5 day workweek as coded.
       Dim rngCell         As Range
       Dim lngStartDate    As Long
       Dim lngEndDate      As Long
       Dim lngNextDay      As Long
       Dim lngWorkDayCount As Long
       Dim lngIncr         As Long
       Dim lngC            As Long
       Dim lngInclusive    As Long
       lngStartDate = CLng(rngInStart.Value)
       lngEndDate = CLng(rngInEnd.Value)
       If IsMissing(vInclusive) Then vInclusive = False
       If vInclusive Then
         lngInclusive = 1
         lngInclusive = 0
       End If
       lngNextDay = lngStartDate
         lngNextDay = lngNextDay + 1
         lngIncr = 1
    'Adjust the following If statement to reflect the normal OFF days in a week...
    'currently set for Saturday (7) and Sunday (1)
         If Weekday(lngNextDay, vbSunday) = 1 Or _
            Weekday(lngNextDay, vbSunday) = 7 Then ' Saturday or Sunday, don't count it
           lngIncr = 0
           For Each rngCell In rngHolidays
              If lngNextDay = CLng(rngCell.Value) Then ' Holiday, don't count it
                lngIncr = 0
                Exit For
              End If
           Next rngCell
         End If
         If lngIncr = 0 Then Debug.Print "Holiday or Sunday: " & CDate(lngNextDay)
            ' increment work day count by 1, or 0 if a Sunday or Holiday
           lngWorkDayCount = lngWorkDayCount + lngIncr
           Debug.Print CDate(lngNextDay); lngWorkDayCount
       Loop Until lngNextDay = lngEndDate + lngInclusive
       SixDayWorkWeekBetween = lngWorkDayCount
    End Function   'SixDayWorkWeekBetween()
    Usage Example:
    Networkdays Inclusive.JPG

    Holidays are included in a separate sheet with a range name (dynamic preferred).

    Note the last argument determines if the count is inclusive/exclusive. Inclusive will look at all the dates including the starting/ending dates. Exclusive will be one less. If the argument is omitted Exclusive is assumed!

    Sample Workbook.
    VBA - Excel - UDF Net Working Days.xlsm

    Last edited by RetiredGeek; 2013-11-23 at 09:04. Reason: Correct spelling
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    afm1985 (2013-11-27)

  4. #3
    Join Date
    Sep 2013
    Thanked 0 Times in 0 Posts
    Thanks you very. It very good !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts