How to
find the number of weekdays between two dates, excluding holidays.
Since he didn’t specify which Office application he was using, I’ll
start with Excel, since that’s the easiest. Believe it or not, the Excel
Analysis ToolPak contains a Visual Basic for Applications (VBA)
function designed for this very purpose!
In a cell, enter:
=NetWorkDays(“01/01/2001”,”03/31/2001”) and tab out of the
cell. It should show 65, the number of weekdays in the
first quarter of 2001. Changing your function to
=NetWorkDays(“01/01/01”,”03/31/01”,{“01/01/01”,”02/19/01”})
eliminates New Year's Day and President's Day, yielding 63 work days.
To eliminate a longer list of holidays, replace the
third function parameter with a range of cells containing
the dates to be excluded. For example,
=NetWorkDays(“01/01/01”,”12/31/01”,E1:E12) where column E
contains a list of holidays in rows 1 through 12.
If the NetWorkDays function is not available, run the
Setup program to install the Analysis ToolPak. After you
install the Analysis ToolPak, you must enable it by using
the Add-Ins command on the Tools menu. More information on
the NetWorkDays function can be found in Excel’s help file.
Public Function WorkDays(D1 As Date, D2 As Date) As Long
Dim vDate As Date, vHolidays(2) As Date, I As Integer
'Initialze variables
vHolidays(1) = "01/01/01"
vHolidays(2) = "02/19/01"
WorkDays = 0
vDate = D1
'Loop through the rnge of dates
While vDate <= D2
If Weekday(vDate) > 1 And Weekday(vDate) < 7 Then
WorkDays = WorkDays + 1
End If
For I = 1 To 2
If vDate = vHolidays(I) Then
WorkDays = WorkDays - 1
End If
Next I
vDate = DateAdd("d", 1, vDate)
Wend
End Function
The array v Holidays could be expanded to hold more dates
and it could be loaded from a table if you wish.