Checking whether it is holiday on the specified date using VBA in Microsoft Excel

In this article, we will create a User Defined Function (UDF) to check whether it is holiday on the specified date, including or excluding Saturdays and Sundays as week offs.

Raw data for this sample consists of dates in sequence in column A. We will find whether it is holiday on the given date, including or excluding week off on Saturday or Sunday.

ArrowRawData

We have specified the holiday dates in the column A on “Holidays” sheet.

ArrowHolidayList

We have created “IsHoliday” custom function to find status of holiday on the given date.

Syntax of function

IsHoliday(Date, InclSaturdays, InclSundays)

InclSaturdays and InclSundays are optional parameters. By default, both have TRUE value. To change Saturday and Sunday into working days, change the value of the respective parameter to FALSE.

ArrowOutput

We have used below formula in cell C9 to find the holiday status for date in cell A9 considering Saturday as a working day.

=IsHoliday(A9,FALSE)

We have used below formula in cell D9 to find the holiday status for date in cell A9 considering Saturdays and Sundays as working days.

=IsHoliday(A9,FALSE,FALSE)

We have used below formula in cell E9 to find the holiday status for date in cell A9 considering Saturdays and Sundays as week offs.

=IsHoliday(A9)

Logic explanation

In the “IsHoliday” function, firstly we check whether given date in the parameter exists in the specified holiday list. If date exists in the holiday list, then return “Holiday” as output. If date doesn’t exists in the holiday list, then check whether given date is a Saturday or a Sunday. Based on the input parameter provided, check whether to include or exclude a Saturday or a Sunday as holidays.

Code explanation

Set RngFind = Worksheets("Holidays").Columns(1).Find(LngDate)

Above code is used to find the location where the specified date exists in the holiday list.

If Not RngFind Is Nothing Then

OK = "Holiday"

GoTo Last

End If

Above code is used to check whether specified date exists in the holiday list. If condition return TRUE, then custom function returns “Holiday” as output and the control shifts to the last line of the UDF.

Please follow below for the code

Option Explicit

Function IsHoliday(LngDate As Date, Optional InclSaturdays As Boolean = True, _
    Optional InclSundays As Boolean = True)

'Declaring variables
Dim RngFind As Range
Dim OK As String

'Initializing the variable
OK = "Working day"

On Error Resume Next

'Finding the location where the specified date exist in the Holidays sheet
Set RngFind = Worksheets("Holidays").Columns(1).Find(LngDate)

On Error GoTo 0

'Checking whether it is holiday on the given date
If Not RngFind Is Nothing Then
    OK = "Holiday"
    GoTo Last
End If

'Checking whether it is Saturday on given date
If InclSaturdays Then
    If Weekday(LngDate, 2) = 6 Then
        OK = "Holiday"
        GoTo Last
    End If
End If

'Checking whether it is Sunday on given date
If InclSundays Then
    If Weekday(LngDate, 2) = 7 Then
        OK = "Holiday"
    End If
End If

Last:
IsHoliday = OK

End Function

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Just Passingby
August 5, 2019 at 8:29 am

Nice. How about this?
Function IsHoliday(LngDate As Date, Optional IsWe As Boolean = True) As Boolean

Dim RngFind As Range
Dim OK As String

'Initializing the variable
IsHoliday = False

On Error Resume Next
'Finding the location where the specified date exist in the Holidays sheet
Set RngFind = Range("Holidays").Find(Int(LngDate)) ''' look only for date even if you also have time
On Error GoTo 0

'Checking whether it is holiday on the given date
If Not RngFind Is Nothing Then IsHoliday = True
'Checking whether it is Saturday on given date
If IsWe And Weekday(LngDate, 2) > 6 Then IsHoliday = True

End Function

Reply ↓