|  

» Calculate holidays using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions
The function below will return True if the input date is a defined holiday.
The function can include or exclude Saturdays and Sundays.
You'll have to customize the function to include the specific holidays for your country.
This function uses the function EasterSunday.
Function IsHoliday(lngDate As Long, InclSaturdays As Boolean, _
    InclSundays As Boolean) As Boolean
' returns True if lngDate is a Norwegian holiday 
' (optionally included Saturdays/Sundays)
' benytter funksjonen EasterSunday
Dim InputYear As Integer, ES As Long, OK As Boolean
    If lngDate <= 0 Then lngDate = Date
    InputYear = Year(lngDate)
    ES = EasterSunday(InputYear)
    OK = True
    Select Case lngDate
        Case DateSerial(InputYear, 1, 1) ' 1. Nyttårsdag
        'Case ES - 4 ' Onsdag før påske
        Case ES - 3 ' Skjærtorsdag
        Case ES - 2 ' Langfredag
        Case ES ' 1. Påskedag
        Case ES + 1 ' 2. Påskedag
        Case DateSerial(InputYear, 5, 1) ' 1. mai
        Case DateSerial(InputYear, 5, 17) ' 17. mai
        Case ES + 39 ' Kristi Himmelfartsdag
        'Case ES + 48 ' Pinseaften
        Case ES + 49 ' 1. Pinsedag
        Case ES + 50 ' 2. Pinsedag
        'Case DateSerial(InputYear, 12, 24) ' Julaften
        Case DateSerial(InputYear, 12, 25) ' 1. Juledag
        Case DateSerial(InputYear, 12, 26) ' 2. Juledag
        'Case DateSerial(InputYear, 12, 31) ' Nyttårsaften
        Case Else
            OK = False
            If InclSaturdays Then
                If WeekDay(lngDate, vbMonday) = 6 Then
                    OK = True
                End If
            End If
            If InclSundays Then
                If WeekDay(lngDate, vbMonday) = 7 Then
                    OK = True
                End If
            End If
    End Select
    IsHoliday = OK
End Function



Rate This Tip
12 34 5
Rating: 2.33     Views: 23144
No comments have been submitted.
Click here to post comment
For Registered Users
Name
Comment Title
Comments