Calculate number of workdays between two dates.

[QUINABA]

Hi All!

Is there a way to calculcate the number of workdays between two dates? I saw DateDiff but it includes weekends, I only want to see number of days without the weekends and if possible without any major holidays. Is there such a way?

Thanks in advance!

 

[Dugantrain]

Well, there is a way, but it's going to require some coding and ultimately using the DateDiff function. Here's an example I whipped up; it tests for January 1st, July 4th, and December 25th, as well as Saturdays and Sundays. You can alter this code to test for Thanksgiving, Memorial Day, etc., but this would be more intensive as these days don't fall on the same date every year; if you want, let me know what Holidays you'd need:

Option Compare Database
Option Explicit

Public Function fCount_NoBiz(dtBeg As Date, dtEnd As Date) As Long
If dtBeg >= dtEnd Then
    fCount_NoBiz = 0
    Exit Function
End If


Dim i As Long
Dim dtInc As Date
i = 0
dtInc = dtBeg
Do While dtInc <= dtEnd
    If Not fIs_NoBiz(dtInc) Then
        i = i + 1
    End If
    dtInc = dtInc + 1
Loop
fCount_NoBiz = DateDiff("D", dtBeg, dtEnd)-i
End Function

Private Function fIs_NoBiz(dtTest As Date) As Boolean
fIs_NoBiz = False
If WeekDay(dtTest) = vbSaturday Or WeekDay(dtTest) = vbSunday Then
    fIs_NoBiz = True
ElseIf Month(dtTest) = 1 And Day(dtTest) = 1 Then
    fIs_NoBiz = True
ElseIf Month(dtTest) = 7 And Day(dtTest) = 4 Then
    fIs_NoBiz = True
ElseIf Month(dtTest) = 12 And Day(dtTest) = 25 Then
    fIs_NoBiz = True
End If
End Function

Private Function fTest_NoBiz()
MsgBox fCount_NoBiz(#1/1/2004#, #12/31/2004#)
End Function

 

[QUINABA]

Thank you very much for your time. I apologize for the late response as I was without a computer since. I'll let you know if I encounter any problems, but it works great.

Thanks again. ray: