Calculate Working Days using VBA in Microsoft Excel

If you have two dates and want to calculate the number of working days between them, excluding Saturdays, Sundays, and Bank Holidays then you can use the following Custom Function.

To use the function type =WORKINGDAYS(A1,B1) - where A1 is your start date and B1 is your end date.

Caution - Only works for bank holidays in England and Wales for the years 2006, 2007, 2008, 2009 and 2010 - however, the code can easily be changed by amending or adding the serials numbers of the bank holidays.

Enter the following code into VBA.

Function WorkingDays(DateFrom, DateTo)
DF = DateFrom
DT = DateTo

Do Until DF > DT

WhatDay = Application.WorksheetFunction.Weekday(DF)

If WhatDay = 7 Then Sat = Sat + 1
If WhatDay = 1 Then Sun = Sun + 1
Days = Days + 1

If DF = 38719 Then BankHoliday = BankHoliday + 1
If DF = 38821 Then BankHoliday = BankHoliday + 1
If DF = 38824 Then BankHoliday = BankHoliday + 1
If DF = 38838 Then BankHoliday = BankHoliday + 1
If DF = 38866 Then BankHoliday = BankHoliday + 1
If DF = 38957 Then BankHoliday = BankHoliday + 1
If DF = 39076 Then BankHoliday = BankHoliday + 1
If DF = 39077 Then BankHoliday = BankHoliday + 1

If DF = 39083 Then BankHoliday = BankHoliday + 1
If DF = 39178 Then BankHoliday = BankHoliday + 1
If DF = 39181 Then BankHoliday = BankHoliday + 1
If DF = 39209 Then BankHoliday = BankHoliday + 1
If DF = 39230 Then BankHoliday = BankHoliday + 1
If DF = 39321 Then BankHoliday = BankHoliday + 1
If DF = 39441 Then BankHoliday = BankHoliday + 1
If DF = 39442 Then BankHoliday = BankHoliday + 1

If DF = 39448 Then BankHoliday = BankHoliday + 1
If DF = 39528 Then BankHoliday = BankHoliday + 1
If DF = 39531 Then BankHoliday = BankHoliday + 1
If DF = 39573 Then BankHoliday = BankHoliday + 1
If DF = 39594 Then BankHoliday = BankHoliday + 1
If DF = 39685 Then BankHoliday = BankHoliday + 1
If DF = 39807 Then BankHoliday = BankHoliday + 1
If DF = 39808 Then BankHoliday = BankHoliday + 1

If DF =39814 Then BankHoliday = BankHoliday + 1
If DF =39913 Then BankHoliday = BankHoliday + 1
If DF =39916 Then BankHoliday = BankHoliday + 1
If DF =39937 Then BankHoliday = BankHoliday + 1
If DF =39958 Then BankHoliday = BankHoliday + 1
If DF =40056 Then BankHoliday = BankHoliday + 1
If DF =40172 Then BankHoliday = BankHoliday + 1
If DF =40175 Then BankHoliday = BankHoliday + 1

If DF =40179 Then BankHoliday = BankHoliday + 1
If DF =40270 Then BankHoliday = BankHoliday + 1
If DF =40273 Then BankHoliday = BankHoliday + 1
If DF =40301 Then BankHoliday = BankHoliday + 1
If DF =40329 Then BankHoliday = BankHoliday + 1
If DF =40420 Then BankHoliday = BankHoliday + 1
If DF =40539 Then BankHoliday = BankHoliday + 1
If DF =40540 Then BankHoliday = BankHoliday + 1

DF = DF + 1

Loop

WorkingDays = Days - Sat - Sun - BankHoliday

End Function

Return to Excel Exchange homepage.