Calculate holidays using VBA in Microsoft Excel

» 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*

CATEGORY: Custom Functions , Printing in VBA	VERSIONS: All Microsoft Excel Versions

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



1	2	3	4	5

RATING: 2.33
VIEWS: 18468

Email Tip

Print Tip

Add to My Tips

No comments have been submitted.

Sponsors:

Book Store:

VIEW ALL BOOKS

Recommended Books:

Related MS EXCEL TIPS:

Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.