Microsoft Office Forums - View Single Post - [Solved] Fifteen Days From Today's Date, (Not Counting Saturday & Sunday).

gmayor · 07-21-2017, 04:53 AM

I think you have made up some of your own VBA syntax there

The immediate problem you have mentioned is the use of Set
The line should be simply iCounter = 0, but then you will find other problems. Use the following function instead, which will add weekdays to a given date - here Today, so add 15 days to that and you get 11th August. The first macro shows how to call the function.

Code:

Sub Macro1()
Selection.TypeText Format(DateDue(Date, 15), "mm-dd-yyyy")
End Sub

Public Function DateDue(ByVal vDate As Date, ByVal iDays As Variant) As Variant
'Graham Mayor - http://www.gmayor.com - Last updated - 14 May 2017
'Add weekdays to date - from Monday if date falls at weekend
Dim iCount As Integer

    DateDue = Null
    If IsNull(vDate) Or IsNull(iDays) Then
        GoTo lbl_Exit
    End If

    Select Case Weekday(vDate)
        Case Is = 1    ' Sunday
            vDate = DateAdd("d", 1, vDate)
            iCount = 0
        Case Is = 7    ' Saturday
            vDate = DateAdd("d", 2, vDate)
            iCount = 0
        Case Else
            iCount = Weekday(vDate) - 2
    End Select

    iDays = iDays + iCount
    vDate = DateAdd("d", iCount * -1, vDate)
    DateDue = DateAdd("d", iDays Mod 5, DateAdd("ww", Int(iDays / 5), vDate))
lbl_Exit:
    Exit Function
End Function