Count the Number of Workdays between Two Dates

[sukishan]

Count the Number of Workdays between Two Dates

Function dhCountWorkdays(ByVal dtmStart As Date, _
ByVal dtmEnd As Date, _
Optional rst As Recordset = Nothing, _
Optional strField As String = "") _
As Integer
    Dim intDays As Integer
    Dim dtmTemp As Date
    Dim intSubtract As Integer
    ' Swap the dates if necessary.
    If dtmEnd < dtmStart Then
        dtmTemp = dtmStart
        dtmStart = dtmEnd
        dtmEnd = dtmTemp
    End If
    ' Get the start and end dates to be weekdays.
    dtmStart = SkipHolidays(rst, strField, dtmStart, 1)
    dtmEnd = SkipHolidays(rst, strField, dtmEnd, -1)
    If dtmStart > dtmEnd Then
        ' Sorry, no workdays to be had. Just return 0.
        dhCountWorkDays = 0
    Else
        intDays = dtmEnd - dtmStart + 1
        ' Subtract off weekend days. Do this by figuring out
        ' how many calendar weeks there are between the dates
        ' and multiplying the difference by two (since there
        ' are two weekend days for each week). That is, if the
        ' difference is 0, the two days are in the same week.
        ' If the difference is 1, then you have two weekend days.
        intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)
        ' The answer, finally, is all the weekdays, minus any
        ' holidays found in the table.
        ' If rst is Nothing, this call won't subtract any dates.
        intSubtract = intSubtract + CountHolidays(rst, _
         strField, dtmStart, dtmEnd)
        dhCountWorkdays = intDays - intSubtract
    End If
End FunctionTo call dhCountWorkdays, pass it two dates (the starting and ending dates). In addition, if you want to take holidays into account, pass it a reference to an open recordset and the name of the field within the recordset containing the holiday date information. For more information on working with this type of function, see the section "Working with Workdays" earlier in this chapter. Unlike the functions presented there, however, this one requires a bit of effort to find the right answer.

There are, of course, many ways to solve this problem. The solution we came up with takes these steps:

Move the starting date forward, skipping weekend and holiday dates, until it finds a workday:
dtmStart = SkipHolidays(rst, strField, dtmStart, 1)Take the same step with the ending date, moving backward.
dtmEnd = SkipHolidays(rst, strField, dtmEnd, -1)If the starting date is now past the ending date, there are no workdays in the interval, so just return 0:
If dtmStart > dtmEnd Then
    ' Sorry, no workdays to be had. Just return 0.
    dhCountWorkdays = 0
intDays = dtmEnd - dtmStart + 1Now for the tricky part, the final three steps:

Subtract the number of weekend days. DateDiff, using the "ww" interval specifier, gives you the number of weeks, and there are two weekend days per weekend:
intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)Subtract the number of holiday days. If you've not supplied a recordset variable, the CountHolidays function returns immediately, reporting no holidays in the interval:
intSubtract = intSubtract + CountHolidays(rst, strField, _
dtmStart, dtmEnd)Finally, return the total number of workdays in the interval:
dhCountWorkdays = intDays - intSubtractTo work with these procedures, you might write a test routine like the one shown in Listing 2.21. This procedure makes these assumptions:

You have Jet and DAO installed on your machine.

You have a reference set to the DAO type library in your project.

You have a database named HOLIDAYS.MDB available (and you've modified the code to point to the actual location of HOLIDAYS.MDB).

HOLIDAYS.MDB includes a table named tblHolidays.

tblHolidays includes a date/time field named Date, containing one row for each holiday you want tracked.