Calculate the count of workdays between two dates

With the custom functions below you can calculate the count of workdays between two dates. Workdays includes normal weekdays except Saturdays and Sundays or holidays e.g. like Easter).

The functions can be used like this in a worksheet cell:

=CountWorkDays(A1,B1)
=AddWorkDays(A1,15)
=DateIsHoliday(A1)

The date input cells must contain valid Excel dates, or formulas/functions that return dates, e.g. =TODAY().

Function CountWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the workday count between two dates
Dim d As Long, dCount As Long
    dCount = 0
    If StartDate < 1 Or EndDate < 1 Then Exit Function

    If StartDate <= EndDate Then
        For d = StartDate To EndDate
            If Not DateIsHoliday(d) Then
                dCount = dCount + 1
            End If
        Next d
    Else
        For d = StartDate To EndDate Step -1
            If Not DateIsHoliday(d) Then
                dCount = dCount + 1
            End If
        Next d
    End If
    CountWorkDays = dCount
End Function

Function AddWorkDays(StartDate As Long, Offset As Long) As Long
' returns a date Offset days from StartDate
Dim d As Long, dCount As Long
    If StartDate < 1 Then Exit Function
    d = StartDate
    If Abs(Offset) > 0 Then
        dCount = 0
        If Offset > 0 Then
            Do
                If Not DateIsHoliday(d) Then
                    dCount = dCount + 1
                End If
                d = d + 1
            Loop Until dCount = Offset
            d = d - 1
        Else
            Do
                If Not DateIsHoliday(d) Then
                    dCount = dCount - 1
                End If
                d = d - 1
            Loop Until dCount = Offset
            d = d + 1
        End If
    End If
    AddWorkDays = d
End Function

Function DateIsHoliday(InputDate As Long) As Boolean
' returns True if InputDate is a Saturday/Sunday or a holiday (Norwegian)
Dim d As Integer, intYear As Integer, lngEasterSunday As Long, OK As Boolean
    OK = False
    If InputDate > 0 Then
        If Weekday(InputDate, vbMonday) >= 6 Then
            OK = True
        End If
        If Not OK Then ' check if InputDate is a holiday
            intYear = Year(InputDate)
            d = (((255 - 11 * (intYear Mod 19)) - 21) Mod 30) + 21
            lngEasterSunday = DateSerial(intYear, 3, 1) + d + (d > 48) + 6 - _
                ((intYear + intYear \ 4 + d + (d > 48) + 1) Mod 7)
            OK = True
            Select Case InputDate
                Case CDate("1.1." & intYear) ' 1. January
                'Case lngEasterSunday - 4 ' Wednesday before Easter
                Case lngEasterSunday - 3 ' Thursday before Easter
                Case lngEasterSunday - 2 ' Friday before Easter
                Case lngEasterSunday ' Easter Sunday
                Case lngEasterSunday + 1 ' Monday after Easter
                Case CDate("1.5." & intYear) ' 1. May
                Case CDate("17.5." & intYear) ' 17. May
                Case lngEasterSunday + 39 ' Kristi Himmelfartsdag
                'Case lngEasterSunday + 48 ' Pinseaften
                Case lngEasterSunday + 49 ' 1. Pinsedag
                Case lngEasterSunday + 50 ' 2. Pinsedag
                'Case CDate("24.12." & intYear) ' Julaften
                Case CDate("25.12." & intYear) ' 1. Juledag
                Case CDate("26.12." & intYear) ' 2. Juledag
                'Case CDate("31.12." & intYear) ' New Years Eve
                Case Else
                    OK = False
            End Select
        End If
    End If
    DateIsHoliday = OK
End Function

Document last updated 2008-09-18 22:17:35 Printerfriendly version

User comments:

nick from maryland wrote (2004-12-09 14:15:44 CET):

Re: GetWorkDays Function not working.
The "=GetWorkDays(A1,A2) format worked for me. Notice the use of , (comma sign) and not ; (semi-colon sign).

Much thanks Ole P for the help.

Ole P. from Norway wrote (2004-12-07 12:11:18 CET):

Re: GetWorkDays Function not working.
You need to pass valid date arguments to the function.
If cell A1 and cell A2 contains two valid Excel date values, you can use the function like this:
=GetWorkDays(A1,A2)

If you don't have any cells with valid date values you can use the function like this:
=GetWorkDays(DATEVALUE("1/1/2004"),DATEVALUE("12/31/2004"))

nick from maryland wrote (2004-12-07 11:39:19 CET):

GetWorkDays Function not working.
I get a #VALUE! error in the cell where I inserted the fuction. I do not know what I am doing wrong. Is there a special thing I have to do before using the function in a worksheet? I inserted the function code in a module as directed. Please somebody help me. By the way, I am using Office 2003.

Thanks for your help.