How to Use WorkDay Function in VBA (With Example)

You can use the WorkDay method in VBA to add or subtract a specific number of working days to a date.

Here is one common way to use this method in practice:

Sub AddWorkDays()

Dim i As Integer

For i = 2 To 10
    Range("C" & i) = WorksheetFunction.WorkDay(Range("A" & i), Range("B" & i))
Next i

End Sub

This particular macro adds the number of working days specified in the range B2:B10 to each date in the range A2:A10 and displays the results in the range C2:C10.

The following example shows how to use this syntax in practice.

Example: How to Use WorkDay Function in VBA

Suppose we have a column of dates in Excel along with another column that specifies the number of working days to add to each date:

We can create the following macro to add the number of working days in column B to each corresponding date in column A:

Sub AddWorkDays()

Dim i As Integer

For i = 2 To 10
    Range("C" & i) = WorksheetFunction.WorkDay(Range("A" & i), Range("B" & i))
Next i

End Sub

When we run this macro, we receive the following output:

By default, column C displays the dates as serial numbers.

To instead display these values as recognizable dates, highlight the range C2:C10, then click the Insert tab along the top ribbon, then click the Number Format dropdown menu and click Short Date:

Each serial number will now be displayed as a date:

Column C displays each date in column A with the specific number of working days in column B added to it.

Note that if we specify a negative number in column B then the WorkDay method will subtract that number of working days from the date in column A.

Note: You can find the complete documentation for the VBA WorkDay method here.