COMPUTING WORK DAYS IN A DATE RANGE

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.