Exclude Holidays in Net Working Days (Excel VBA)

No of Working days in a Year / quarter using VBA (Excluding Holidays)

Many times we are confronted with a situation to estimate the days left in a quarter or year. The catch is the holidays, exclude Christmas, Thanksgiving, Martin Luthers day or Diwali from the working day. Here is a where Excel 2007 has simplified that for us

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Here the holidays are excluded from the predefined range.

Function Get_Net_Working_Days_Excluding_Holidays()

Dim WrkDays As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = #12/12/2008#
WrkDays = WorksheetFunction.NetworkDays(StartDate, EndDate, Range("b2:b15"))

MsgBox "No of Working Days Left := " & WrkDays

End Function

The function is exclusive in Excel 2007. There is no equivalent function in Excel 2003

Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text