Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Mechanicsville, Virginia, USA
    Thanked 0 Times in 0 Posts

    Networkdays (Excel 2003)

    Hans - you wrote this marvelous piece of code for me. How could I alter this to also exclude Sundays?

    Public Function sixdayworkweekbetween(rngInStart As Range, rngInEnd As Range, rngHolidays As Range) As Long
    ' Arguments are Start Date cell, End Date cell, and range of Holidays in valid Excel date format
    Dim rngCell As Range
    Dim lngStartDate As Long, lngEndDate As Long, lngNextDay As Long, lngWorkDayCount As Long
    Dim lngIncr As Long, lngC As Long

    lngStartDate = CLng(rngInStart.Value)
    lngEndDate = CLng(rngInEnd.Value)

    lngNextDay = lngStartDate
    lngNextDay = lngNextDay + 1
    lngIncr = 1
    If Weekday(lngNextDay, vbMonday) = 2 Then ' Monday, 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
    sixdayworkweekbetween = lngWorkDayCount

    End Function


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Networkdays (Excel 2003)

    In fact, it was <!profile=JohnBF>JohnBF<!/profile>, not I who wrote this function for you - see <post:=656,446>post 656,446</post:>.

    You have modified it incorrectly - as it is now, it skips Tuesdays, not Mondays:

    If Weekday(lngNextDay, vbMonday) = 2 Then

    With the vbMonday argument, Monday is the first day of the week, Tuesday the second day, etc. To skip Mondays, use

    If Weekday(lngNextDay, vbSunday) = 2 Then

    To skip both Sundays and Mondays, use

    If Weekday(lngNextDay, vbSunday) <= 2 Then

    Note: please read this reply in the Lounge itself. The e-mail version may be garbled.

Posting Permissions

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