The following function does this, and also caters, optionally, for public
holidays. Moreover it handles the different public holidays in the
constituent counties of the UK and of the Republic of Ireland by means of the
Country argument.
Public Function WorkDaysDiff(varLastDate As Variant, _
varFirstDate As Variant, _
strCountry As String, _
Optional blnExcludePubHols As Boolean = False)
As Variant
Dim lngDaysDiff As Long, lngWeekendDays As Long
Dim intPubHols As Integer
If IsNull(varLastDate) Or IsNull(varFirstDate) Then
Exit Function
End If
' if first date is Sat or Sun start on following Monday
Select Case WeekDay(varFirstDate, vbMonday)
Case vbSaturday
varFirstDate = varFirstDate + 2
Case vbSunday
varFirstDate = varFirstDate + 1
End Select
' if last date is Sat or Sun finish on following Monday
Select Case WeekDay(varLastDate, vbMonday)
Case vbSaturday
varLastDate = varLastDate + 2
Case vbSunday
varLastDate = varLastDate + 1
End Select
' get total date difference in days
lngDaysDiff = DateDiff("d", varFirstDate, varLastDate)
' get date difference in weeks and multiply by 2
' to get number of weekend days
lngWeekendDays = DateDiff("ww", varFirstDate, varLastDate, vbMonday) * 2
' subtract number of weekend days from total date difference
' to return number of working days
WorkDaysDiff = lngDaysDiff - lngWeekendDays
' exclude public holidays if required
If blnExcludePubHols Then
intPubHols = DCount("*", "qryPubHols", "HolDate Between #" _
& Format(varFirstDate, "mm/dd/yyyy") & "# And #" & _
Format(varLastDate - 1, "mm/dd/yyyy") & "#" & _
" And Country = """ & strCountry & """")
WorkDaysDiff = WorkDaysDiff - intPubHols
End If
End Function
The qryPubHols query referred to in the function is:
SELECT Countries.Country, PubHols.HolDate
FROM PubHols INNER JOIN (Countries INNER JOIN PubHols_Country
ON Countries.CountryID = PubHols_Country.CountryID)
ON PubHols.HolDateID = PubHols_Country.HolDateID;
As you see this is based on 3 tables: Countries with columns CountryID and
Country,
PubHols_Country with columns CountryID and HolDateID, and PubHols with
columns HolDateID and HolDate. It was first written many years ago; if I
were doing it now I'd get rid of the HolDateID and CountryID columns and use
Country and HolDate as the keys. The query would not then only need to
include the PubHols_Country table. In fact the table itself could be the
domain argument of the DCount function.
If you are only concerned with one set of UK public holidays you can remove
the Country argument from the function and just have a single-column PubHols
table. On the other hand, while it was originally designed for UK/Republic
of Ireland use, it can be extended internationally simply by inserting more
rows into the three tables with additional countries and their public holiday
dates.
Ken Sheridan
Stafford, England