Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
datediff("d", dtFrom, dtTo) - (2 * datediff("ww", dtFrom, dtTo))
Open in new window
The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.
do you think I can do this in one Query?
Public Function ISO_WorkdayDiff( _
ByVal varDateFrom As Variant, _
ByVal varDateTo As Variant, _
Optional ByVal booExcludeHolidays As Boolean) _
' Purpose: Calculate number of working days between dates datDateFrom and datDateTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' May be freely used and distributed.
' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
' Option for 5 or 6 working days per week added.
' 2008-06-12. Option to exclude holidays from the count of workdays.
' 2019-02-07. Modified for use from query.
Const cbytWorkdaysOfWeek As Byte = 5
' Name of table with holidays.
Const cstrTableHoliday As String = "tblHoliday"
' Name of date field in holiday table.
Const cstrFieldHoliday As String = "HolidayDate"
Dim datDateFrom As Date
Dim datDateTo As Date
Dim bytSunday As Byte
Dim intWeekdayDateFrom As Integer
Dim intWeekdayDateTo As Integer
Dim lngDays As Long
Dim datDateTemp As Date
Dim strDateFrom As String
Dim strDateTo As String
Dim lngHolidays As Long
Dim strFilter As String
Dim bolDatesReversed As Boolean
If IsDate(varDateFrom) And IsDate(varDateTo) Then
datDateFrom = CDate(varDateFrom)
datDateTo = CDate(varDateTo)
' Reverse dates if these have been input reversed.
If datDateFrom > datDateTo Then
datDateTemp = datDateFrom
datDateFrom = datDateTo
datDateTo = datDateTemp
bolDatesReversed = True
bolDatesReversed = False
' Find ISO weekday for Sunday.
bytSunday = Weekday(vbSunday, vbMonday)
' Find weekdays for the dates.
intWeekdayDateFrom = Weekday(datDateFrom, vbMonday)
intWeekdayDateTo = Weekday(datDateTo, vbMonday)
' Compensate weekdays' value for non-working days (weekends).
intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
' Calculate number of working days between the two weekdays, ignoring number of weeks.
lngDays = intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
' Add number of working days between the weeks of the two dates.
lngDays = lngDays + (cbytWorkdaysOfWeek * DateDiff("w", datDateFrom, datDateTo, vbMonday, vbFirstFourDays))
If booExcludeHolidays And lngDays > 0 Then
strDateFrom = Format(datDateFrom, "yyyy\/mm\/dd")
strDateTo = Format(datDateTo, "yyyy\/mm\/dd")
strFilter = cstrFieldHoliday & " Between #" & strDateFrom & "# And #" & strDateTo & "# And Weekday(" & cstrFieldHoliday & ", 2) <= " & cbytWorkdaysOfWeek & ""
lngHolidays = DCount("*", cstrTableHoliday, strFilter)
If bolDatesReversed = True Then
ISO_WorkdayDiff = -(lngDays - lngHolidays)
ISO_WorkdayDiff = lngDays - lngHolidays
ISO_WorkdayDiff = Null
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
NumberOfDays1: IIf([Date1]+[Date2] Is Null;Null;Val(ISO_WorkdayDiff([Date2],[Date1],True))
From novice to tech pro — start learning today.
Members can enroll in this course at no extra cost.