CODEWITHSUNDEEP
vbaexcelfunction
New2VBA
New2VBA

Reputation: 347

Workday Function to add Workdays

I'm trying to run code based off of dates in the D column. The code below works. But the "if" part of the conditional formatting needs to be + 30 workdays, not plus thirty days. I'm assuming that the WORKDAY function helps with this. But when I try + workday(30) and things like that, I don't get anywhere.

For Each oKey In oDictionary.keys
    Editing_Sheet.Range("A1").CurrentRegion.AutoFilter Field:=1,    Criteria1:=CStr(oKey)
    LastRowFiltered = Editing_Sheet.Cells(Rows.Count, "A").End(xlUp).Row
        If Range("D" & LastRowFiltered) <= Date + 30 Then
         'run code'

Upvotes: 2

Views: 29469

Answers (2)

Daniel
Daniel

Reputation: 515

To build on @Gary's Student's answer, here's an example written as a VBA function, returning the calculated date. So, if you want to feed it a date and get one back, or need to loop through an array or range of cells, might be useful.

'Workday Function via VBA
Function WorkDayCalc(ByVal d1 As Date, ByVal days As Integer) As Date
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    WorkDayCalc = wf.WorkDay(d1, days)
End Function

'Example Usage
Sub Test_WorkDayCalc()
    Dim d As Date, i As Integer
    d = CDate("01/03/2022")
    i = 30
    MsgBox "Start " + CStr(d) & vbCrLf & "End  " + CStr(WorkDayCalc(d, i))
End Sub

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

To use the worksheet Workday() function within VBA:

Sub WhyWork()
    Dim d1 As Date, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    d2 = wf.WorkDay(Date, 30)
    MsgBox Date & vbCrLf & d2
End Sub

Upvotes: 12

Related Questions