Excel VBA: Create a series of workdays only

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 (341 downloads )