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.
'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.
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")
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.