Excel Add WorkDays To Date – Excluding Weekends & Holidays

Adding WeekDays to Date – Excel Formula & VBA Code

This is helpful in planning duration for a project, budget or anything that is related to number of working days.

You can quickly add Days to start date also exclude the Weekends & Holidays, in Worksheet & VBA using the same function.

Lets see how these functions solve our problem.

1. Excel Worksheet Formula – WorkDay

Syntax: WorkDay (start date, number of days, [holidays])

Lets see the working of this function with an example. Assume you have these details.

• Start date of the project/task: “30-Apr-2018”
• Duration of the project is 45 days.
• Holiday(s): “1-May-2018”

Type this function in Excel.

=TEXT(WORKDAY("30-Apr-2018",45,"1-May-2018"),"dd-mmm-yyyy")
OR
=TEXT(WORKDAY(A1,B1,A2:A10),"dd-mmm-yyyy")
'A1 - Start date
'B1 - Number of Days to be Added to the Date in A1
'A2:A10 - List of Holiday dates

It will just add days, exclude weekends, holidays & gives you an end date.

Note: Workday returns the result as a numeric datevalue. “Text” function is used to convert that datevalue into proper date format.

2. Adding Days to Date in VBA

Don’t use VBA.DateAdd function in Excel macro code for this purpose.

Instead, use the same Workday function in VBA as given below.

Sub Add_WorkDays_To_Date()
    Dim oRng As Range, iDate As Date, iHoliday As Range, iDays As Range
    Set iRng = ThisWorkbook.Sheets(1).Range("C1")
    iDate = ThisWorkbook.Sheets(1).Range("A1")
    Set iDays = ThisWorkbook.Sheets(1).Range("B1")
    Set iHoliday = ThisWorkbook.Sheets(1).Range("A2:A2")

    iRng.Value = VBA.Format(Application.WorksheetFunction.WorkDay(iDate, iDays, iHoliday), "dd-mmm-yyyy")
End Sub

Using Application.WorksheetFunction, you can call any worksheet formula inside Excel macro.

Note: Instead of “Text” formula, we have used “Format” function in VBA. But both serve the same purpose.

Here, the result will be the same as how it appears in the Formula result.