# 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.

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

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.

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.

```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

```

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

1. Just Passingby

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

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.