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

    Networkdays (Excel 2003)

    I have looked through prior posts to see if I could find something that you help me calculate the number of networkdays (inlcuding saturdays) for a given month. Does anyone have a piece of VBA that I could have that would count Satrudays?

    I appreciate any assistance that you can provide.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Networkdays (Excel 2003)

    <P ID="edit" class=small>(Edited by JohnBF on 19-Jun-07 13:14. )</P>This user-defined function should return the number of workdays, including Saturdays, excluding Sundays and specified Holidays in a range, between any two dates. Please test it to be certain it works correctly (which is why the Debug.Print lines are in there - remove them if you are confident it's correct):

    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

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

    lngNextDay = lngStartDate
    Do
    lngNextDay = lngNextDay + 1
    lngIncr = 1
    If Weekday(lngNextDay, vbSunday) = 1 Then ' Sunday, don't count it
    lngIncr = 0
    Else
    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Networkdays (Excel 2003)

    Thanks John

    I have added your piece of code to my workbook, but cannot write a formula using this function. In the past, when functions were added to a database, I could use the function name to write my formula. Can you let me know what I am doing wrong.

    Thanks

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Networkdays (Excel 2003)

    Macros must be enabled, and the function must be in a standard Module (not "ThisWorkbook"). If you put the function in a Module in your "personal.xls" workbook, you must write the formula this way:

    =personal.xls!sixdayworkweekbetween(arg1,arg2,arg3 )

    Attached is a spreadsheet example, if it helps.

    Not sure what you mean by "when functions were added to a database", but many people keep functions such as this in personal.xls, if that is what you are referring to.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Networkdays (Excel 2003)

    Thanks John! Now that I have it set up correctly it works like a charm. Have a super day!

Posting Permissions

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