Work Days Between Two Dates Without Cycling Through Dates
Join the DZone community and get the full member experience.Join For Free
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 FUNCTIONVBA 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
Opinions expressed by DZone contributors are their own.