The Nextworkday(date, optional holidays) function returns the date for the next workday.
The first parameter is the starting date you set manually.
The second parameter is optional and points to a range with dates you want to exclude from the workdays list, like easter or your vacation.
If you omit the second parameter, only Saturdays and Sundays are excluded from the list.
You can download the workbook at the bottom of this article
Function EasterDate
The EasterDate calculates the 1st date of easter for the year you send as parameter, and helps set up the holiday list.
Function EasterDate code
'Find 1st day of easter
Public Function EasterDate(Yr As Integer) As Date
Dim x As Integer
x = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + x + (x > 48) + 6 - ((Yr + Yr \ 4 + x + (x > 48) + 1) Mod 7)
End Function
Usage of the EasterDate function in a worksheet formula
Function NextWorkDay code
Function NextWorkday(ThisDate As Date, Optional rngHolidays As Range) As Date
Dim x As Long
Dim y As Long
Dim DayNum As Long
Dim NextDate As Date
Dim Found As Boolean
Dim NotDate As Long
With rngHolidays
'Week starts on Sunday
x = 0
Do
x = x + 1
'Check next date
NextDate = ThisDate + x
'Find day of week
DayNum = Weekday(NextDate, vbSunday)
'Reset not date counter
NotDate = 0
'Reset found flag
Found = False
'Ignore holiday check if parameter is omitted
If Not rngHolidays Is Nothing Then
'Start on first row in range
y = 1
While Found = False And y <= .Rows.Count And NotDate < 10
'Check if date
If IsDate(.Cells(y, 1)) Then
'Reset not date counter every time a date is found
NotDate = 0
'Date present in holiday's list?
If DateValue(.Cells(y, 1)) = NextDate Then
Found = True
End If
Else
'This row wasn't a date
NotDate = NotDate + 1
End If
y = y + 1
Wend
End If
'Loop if sat, sun or found in holiday list
Loop Until (DayNum <> 7 And DayNum <> 1) And Found = False
End With
NextWorkday = NextDate
End Function
Usage of the NextWorkDay function in a worksheet formula:
Download the workbook: Create a series of workdays only (447 downloads )