Networkdays (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

(Edited by JohnBF on 19-Jun-07 13:14. )

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote