DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world
Work Days Between Two Dates Without Cycling Through Dates
I was thinking about how to optimize figuring out the work days between two dates and came up with this function. It doesn't take into account holidays. You would have to take out the workdays for holidays from the number if you want to take into account holidays, but that should be easy enough.
4GL Version
# workdays
# returns the number of working days between two dates
FUNCTION workdays( dt_begin, dt_end )
DEFINE
dt_begin DATE,
dt_end DATE,
dt_first_sunday DATE,
dt_last_saturday DATE,
int_workdays INTEGER
# get first sunday
LET dt_first_sunday = dt_begin + ((7 - WEEKDAY(dt_begin)) MOD 7)
# get last saturday
LET dt_last_saturday = dt_end + ((-1 * (WEEKDAY(dt_end) + 1)) MOD 7)
# get work weeks between first sunday and last saturday
LET int_workdays = (((dt_last_saturday - dt_first_sunday) + 1) / 7) * 5
# if first sunday is not begin date
IF dt_first_sunday <> dt_begin THEN
# assume first sunday is after begin date
# add workdays from begin date to first sunday
LET int_workdays = int_workdays + (6 - WEEKDAY(dt_begin))
END IF
# if last saturday is not end date
IF dt_last_saturday <> dt_end THEN
# assume last saturday is before end date
# add workdays from last saturday to end date
LET int_workdays = int_workdays + WEEKDAY(dt_end)
END IF
# return working days
RETURN int_workdays
END FUNCTION
VBA Version
' WorkDays
' returns the number of working days between two dates
Public Function WorkDays(ByVal dtBegin As Date, ByVal dtEnd As Date) As Long
Dim dtFirstSunday As Date
Dim dtLastSaturday As Date
Dim lngWorkDays As Long
' get first sunday in range
dtFirstSunday = dtBegin + ((8 - Weekday(dtBegin)) Mod 7)
' get last saturday in range
dtLastSaturday = dtEnd - (Weekday(dtEnd) Mod 7)
' get work days between first sunday and last saturday
lngWorkDays = (((dtLastSaturday - dtFirstSunday) + 1) / 7) * 5
' if first sunday is not begin date
If dtFirstSunday <> dtBegin Then
' assume first sunday is after begin date
' add workdays from begin date to first sunday
lngWorkDays = lngWorkDays + (7 - Weekday(dtBegin))
End If
' if last saturday is not end date
If dtLastSaturday <> dtEnd Then
' assume last saturday is before end date
' add workdays from last saturday to end date
lngWorkDays = lngWorkDays + (Weekday(dtEnd) - 1)
End If
' return working days
WorkDays = lngWorkDays
End Function
