How to calculate difference between 2 dates/time excluding weekends ? (1 Viewer)

=dhCountWorkdaysA([YourStartDate],[YourEndDate])

dhCountWorkdaysA([Text2],[Text4])

Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd As Date, _
 Optional adtmDates As Variant = Empty) _
 As Integer

   [COLOR="DarkGreen"] ' Count the business days (not counting weekends/holidays) in
    ' a given date range.
    
    ' Modified from code in
    ' "Visual Basic Language Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 2000; Sybex, Inc. All rights reserved.
    
    ' Requires:
    '   SkipHolidays
    '   CountHolidays
    '   IsWeekend
    
    ' In:
    '   dtmStart:
    '       Date specifying the start of the range (inclusive)
    '   dtmEnd:
    '       Date specifying the end of the range (inclusive)
    '       (dates will be swapped if out of order)
    '   adtmDates (Optional):
    '       Array containing holiday dates. Can also be a single
    '       date value.
    ' Out:
    '   Return Value:
    '       Number of working days (not counting weekends and optionally, holidays)
    '       in the specified range.
    ' Example:
    '   Debug.Print dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, _
    '    Array(#1/1/2000#, #7/4/2000#))
    '
    '   returns 2, because 7/2/2000 is Sunday, 7/4/2000 is a holiday,
    '   leaving 7/3 and 7/5 as workdays.[/COLOR]
    
    Dim intDays As Integer
    Dim dtmTemp As Date
    Dim intSubtract As Integer
    
    [COLOR="DarkGreen"]' Swap the dates if necessary.>[/COLOR]
    If dtmEnd < dtmStart Then
        dtmTemp = dtmStart
        dtmStart = dtmEnd
        dtmEnd = dtmTemp
    End If
    
    [COLOR="DarkGreen"]' Get the start and end dates to be weekdays.[/COLOR]
    dtmStart = SkipHolidaysA(adtmDates, dtmStart, 1)
    dtmEnd = SkipHolidaysA(adtmDates, dtmEnd, -1)
    If dtmStart > dtmEnd Then
       [COLOR="DarkGreen"] ' Sorry, no Workdays to be had. Just return 0.[/COLOR]
        dhCountWorkdaysA = 0
    Else
        intDays = dtmEnd - dtmStart + 1
        
       [COLOR="DarkGreen"] ' Subtract off weekend days.  Do this by figuring out how
        ' many calendar weeks there are between the dates, and
        ' multiplying the difference by two (because there are two
        ' weekend days for each week). That is, if the difference
        ' is 0, the two days are in the same week. If the
        ' difference is 1, then we have two weekend days.[/COLOR]
        intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)
        
       [COLOR="DarkGreen"] ' The answer to our quest is all the weekdays, minus any
        ' holidays found in the table.[/COLOR]
        intSubtract = intSubtract + _
         CountHolidaysA(adtmDates, dtmStart, dtmEnd)
        
        dhCountWorkdaysA = intDays - intSubtract
    End If
End Function