I use the following function, which uses a table Holidays with a column
HolDate of date/time data type. The reason for the name of the function BTW
is that I also have one which takes a Country as another argument and allows
for different sets of public holidays in different countries:
Public Function WorkDaysDiff_SingleCountry(varFirstDate As Variant, _
varLastDate As Variant, _
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, vbSunday)
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, vbSunday)
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_SingleCountry = lngDaysDiff - lngWeekendDays
' exclude public holidays if required
If blnExcludePubHols Then
intPubHols = DCount("*", "Holidays", "HolDate Between #" _
& Format(varFirstDate, "mm/dd/yyyy") & "# And #" & _
Format(varLastDate - 1, "mm/dd/yyyy") & "#")
WorkDaysDiff_SingleCountry = WorkDaysDiff_SingleCountry - intPubHols
End If
End Function
The optional blnExcludePubHols argument allows for public holidays to be
taken into account or not. By default its False, so in your query, to
exclude weekends and holidays from the count you'd call it like so:
SELECT
AVG(WorkDaysDiff_SingleCountry([DateReceived],[FirstContactAttempt],TRUE))
AS RectToFirstContact
FROM Journey_MI;
Ken Sheridan
Stafford, England
macroapa said:
Hi,
I have found the following post for calculating the diff between 2
dates excluding holidays and weekends:
http://groups.google.co.uk/group/microsoft.public.access/msg/1456554bfa782204?hl=en
Now I can get it to work for just weekends, but am struggling with the
3rd argument for holidays.
I have a table called 'Holidays' which contains a list of the dates so
thought the code would be:
SELECT Avg(dhCountWorkdaysA([DateReceived],[FirstContactAttempt],
[Holidays])) AS RectToFirstContact
FROM Journey_MI;
However, it is bringing up the input box for holidays so isn't
recognising it correctly.
Does anyone have any ideas?
Thanks.
