In this article, we will create a User Defined Function (UDF) to count the number of working days between the specified dates, including or excluding Saturdays and Sundays as week offs.
Raw data for this example consists of a start date and an end date. We want to count the number of working days between these dates.
We have specified the holiday dates in the column A in “Holidays” sheet.
Excel has inbuilt function, NETWORKDAYS to count the number of working days between the interval.
Syntax of NETWORKDAYS function
This function will exclude the date specified in the Holidays list, while counting the number of working days.
This function considers Saturdays and Sundays as Week offs by default, so we can't count the number of working days, in case we have only one Week off.
We have created “CountWorkingDays” custom function to count the number of working days between the interval. This custom function handles the problem of the NETWORKDAYS function. In this function, we can count the number of working days even if there is only one Week off either on Saturday or Sunday.
Syntax of custom function
CountWorkingDays (StartDate, EndDate, InclSaturdays, InclSundays)
InclSaturdays and InclSundays are optional parameters. By default, both have TRUE values. To change Saturdays and Sundays into working days, change the value of the respective parameter to FALSE.
Microsoft introduced NETWORKDAYS.INTL function with Excel 2010. This function handles the problem of NETWORKDAYS function. In this function, we can specify the Week off days. We can either specify one or two days as Week off.
Syntax of NETWORKDAYS.INTL function
NETWORKDAYS.INTL(StartDate, EndDate, [Weekend], [Holidays])
In the weekend parameter, we can specify Week off days.
In this example, we will use all the above three functions to count the number of working days.
In the “CountWorkingDays” function, firstly we check whether given date in the parameter exists in the specified holiday list. If the date exists in the holiday list, then that day is not counted in the number of working days. If the date doesn’t exists in the holiday list, then check whether given date is Saturday or Sunday. Based on the input parameter provided, check whether to include or exclude Saturdays or Sundays as holiday.
Set RngFind = Worksheets("Holidays").Columns(1).Find(i)
Above code is used to find the location where the specified date exists in the holiday list.
If Not RngFind Is Nothing Then
Above code is used to check whether specified date exists in the holiday list. If condition returns TRUE, then that day is not counted in the number of working days.
Please follow below for the code
Option Explicit Function CountWorkingDays(StartDate As Long, EndDate As Long, Optional InclSaturdays As Boolean = True, _ Optional InclSundays As Boolean = True) 'Declaring variables Dim RngFind As Range Dim i As Long For i = StartDate To EndDate On Error Resume Next 'Finding the location where the specified date exist in the Holidays sheet Set RngFind = Worksheets("Holidays").Columns(1).Find(i) On Error GoTo 0 'Checking whether it is holiday on the given date If Not RngFind Is Nothing Then GoTo ForLast End If 'Checking whether it is Saturday on given date If InclSaturdays Then If Weekday(i, 2) = 6 Then GoTo ForLast End If End If 'Checking whether it is Sunday on given date If InclSundays Then If Weekday(i, 2) = 7 Then GoTo ForLast End If End If CountWorkingDays = CountWorkingDays + 1 ForLast: Next 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 email@example.com
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.