• Home
 · 
• Miscellaneous
 · 
• An Easy way to calculate the number of business days less holidays between two dates.

An Easy way to calculate the number of business days less holidays between two dates.

An Easy way to calculate the number of business days less holidays between two dates.

Rate An Easy way to calculate the number of business days less holidays between two dates.

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

Dim n As Date
Dim x As Integer
Dim daycount As Integer

'Place all the holidays below if you need more than 50 dates
' simply increase the size of the array from Holiday(50) to
'holiday(100) etc. or use a dynamic array. 
DO NOT ENTER HOLDIAYS WHICH FALL OUT ON
'SATURDAY or SUNDAY
'

Dim holiday(50) As Date
holiday(0) = #9/3/2001#
holiday(1) = #7/4/2001#
holiday(2) = #10/8/2001#
holiday(3) = #11/22/2001#
holiday(4) = #12/25/2001#
holiday(5) = #12/24/2001#
holiday(6) = #1/1/2002#
holiday(7) = #1/21/2002#
holiday(8) = #2/18/2002#
holiday(9) = #5/27/2002#
holiday(10) = #7/4/2002#

'http://msdn.microsoft.com/library/en-us/dnima99/html/hours.asp
'The following two lines which calculate business days
'was obtained from the above URL.
daycount = DateDiff("d", fdate, ldate) - _
    (DateDiff("ww", fdate, ldate) * 2) + 1

    For n = fdate To ldate
        For x = 0 To UBound(holiday)
          If n = holiday(x) Then
          daycount = daycount - 1
          End If
        Next x
    Next n
    
    TaTime = daycount
    
    


End Function
 'Programmers Note: The following function operates by counting the days between
 'two dates and then subtracting the number of Sundays  between
 'those two dates TIMES 2. In addition, an array is
 'added to subtract holidays. IMPORTANT: this function may give incorrect data if
 'the opening and or closing dates are on either Sunday or Saturday. IF this
 'precision is required please contact the function's Author.([email protected])
 'The code is slightly more complex.