Option Explicit

Sub Macro6()
'The Idea is to Find Weekdays, then find Holidays that are Weekdays
'Graham Mayor - https://www.gmayor.com - Last updated - 05 Jan 2021
Dim Count As Integer
Dim TestDay As Date
Dim sMessage As String
Dim vHolidays As Variant
Dim i As Integer, j As Integer
Dim oRng As Range
Dim dDate As DataObject

vHolidays = Array("01/01/21", "01/02/21", "01/30/21")    'add the holidays to the array

    Count = 14
    TestDay = Date    'CDate("01/01/21")

    For i = 1 To 14
        If Weekday(TestDay + i) = 1 Or Weekday(TestDay + i) = 7 Then
            Count = Count + 1
            'if added day is at the weekend add another day
            If Weekday(TestDay + Count) = 1 Or Weekday(TestDay + Count) = 7 Then Count = Count + 1
        End If
    Next i

    For j = 0 To UBound(vHolidays)
        If TestDay = CDate(vHolidays(j)) Then
            Count = Count + 1
            'probably unnecessary but if added day is at the weekend add another day
            If Weekday(TestDay + Count) = 1 Or Weekday(TestDay + Count) = 7 Then Count = Count + 1
        End If
    Next j

    TestDay = DateAdd("d", Count, TestDay)     'Add count to TestDay

    Selection.TypeText TestDay    'optional as the date is written to the clipboard

    Set dDate = New DataObject
    dDate.SetText TestDay
    dDate.PutInClipboard

    sMessage = "14 Working Days from Today - " & TestDay
    MsgBox sMessage, vbInformation, "Add working days"
    Set dDate = Nothing
End Sub

Sub Test()
  MsgBox Format(fcnAdvancedDate("1/6/2021"), "MMMM dd, yyyy")
  MsgBox fcnAdvancedDate
End Sub


Function fcnAdvancedDate(Optional StartDate As Variant, Optional DaysToCount As Long = 14) As Date
Dim lngIndex As Long
Dim dateTmp As Date
Dim varHolidays As Variant
Dim colHolidays As New Collection
 varHolidays = Array("01/01/21", "01/18/21", "02/15/21")
 dateTmp = Date
 If IsDate(StartDate) Then dateTmp = StartDate
 For lngIndex = 0 To UBound(varHolidays)
   colHolidays.Add varHolidays(lngIndex), varHolidays(lngIndex)
 Next lngIndex
 lngIndex = 0
 Do
   dateTmp = DateAdd("d", 1, dateTmp)
   Select Case Weekday(dateTmp)
     Case 2 To 6
       On Error Resume Next
       colHolidays.Add Format(dateTmp, "MM/dd/yy"), Format(dateTmp, "MM/dd/yy")
       If Err.Number = 0 Then
         lngIndex = lngIndex + 1
         colHolidays.Remove (colHolidays.Count)
       End If
   End Select
 Loop Until lngIndex = DaysToCount
 fcnAdvancedDate = dateTmp
lbl_Exit:
  Exit Function
End Function

Sub SubSimple()
  'You can run this sub directly.
  MsgBox "Hello Stephen"
End Sub
Sub WithParameters(strGreeting As String, strName As String)
  'This sub has required parameters it can only be called by another sub passing arguments.
  MsgBox strGreeting & " " & strName
End Sub
Sub WithOptionalParameters(Optional strGreeting As String = "Hello", Optional strName As String = "Stephen")
  'This sub has optional parameters it can only be called by another sub with or with passing arguments.
  MsgBox strGreeting & " " & strName
End Sub
Function fcnOffsetDate(Optional StartDate, Optional lngOffset As Long = 7) As Date
'Functions return a value to the the calling procedure.
'This functions have optional parameters. The optional parameters define the defaults.
Dim dateTmp As Date
  dateTmp = Date
  If IsDate(StartDate) Then dateTmp = StartDate
  fcnOffsetDate = DateAdd("d", lngOffset, dateTmp)
End Function

Sub CallingSub()
  'Call each of the examples. Step through this procedure using the F8 key.
  SubSimple
  WithParameters "Hello", "Steven Ray" 'the arguments must be passed.
  WithOptionalParameters 'The parameters are optional so no arguments have to be passed.
  MsgBox fcnOffsetDate 'The parameters are optional so no arguments have to be passed.
  MsgBox fcnOffsetDate("12/31/2021", 25) 'We are passing optional arguments.
End Sub