How to Add Days and Skip Weekends or Holidays using Excel VBA
Question details
The user needs to calculate a future date by adding a set number of calendar days, with a rule that if the calculated date lands on a weekend or a holiday, it adjusts backward until a valid business day is found.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating accurate business deadlines where a fixed calendar duration is added, but the final due date cannot fall on non-working days.
- Observed behavior
- A custom VBA macro is required to perform the calculation, check the resulting date against weekends and a custom holiday worksheet, and loop backward to a valid date.
Ensure you have a dedicated worksheet named 'Holidays' containing your specific holiday dates, and verify that macros are enabled in your Excel workbook before running the VBA code.
Use a VBA Do While Loop to Calculate Valid Business Days
Create a custom VBA function that uses DateAdd to calculate the initial date, and then loops backward to find the nearest valid business day if the result is a weekend or holiday.
This approach uses a single streamlined function. It adds your target days to the start date and immediately checks if the result is a Saturday, Sunday, or matches a date found in your 'Holidays' worksheet.
Press ALT + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.
In the top menu, click on Insert, then select Module to create a blank workspace for your new custom function.
Copy and paste the following code into the module: Public Function AddBusinessDate(StartDate As Date, Days As Integer) As Date Dim d As Date d = DateAdd("d", Days, StartDate) Do While Weekday(d) = vbSaturday Or Weekday(d) = vbSunday Or IsDate(Application.Match(CLng(d), Worksheets("Holidays").Columns(2), 0)) d = d - 1 Loop AddBusinessDate = d End Function
Return to your Excel spreadsheet. Type `=AddBusinessDate(A1, 60)` into a cell, replacing A1 with your starting date cell and 60 with the number of days you want to add.
Use Helper Functions for Better Code Readability
Break down the VBA logic into separate IsWeekend and IsHoliday helper functions. This makes the code easier to maintain and troubleshoot.
Use WPS Spreadsheet for Advanced Macro and VBA Calculations
WPS Office perfectly supports Microsoft Excel macros and VBA scripts. You can easily implement these custom date calculation functions to handle complex holiday and weekend exclusions directly in WPS Spreadsheet.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet or create a new one.
- 2. Access the Macro Editor: Navigate to the Tools tab on the ribbon and click on 'Macros', or press ALT + F11 to open the WPS VBA Editor.
- 3. Insert Your Code: Right-click your project, select Insert > Module, and paste your business date calculation VBA code.
- 4. Save as XLSM: Go to File > Save As and select 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your custom functions are preserved.
Frequently Asked Questions
Can I use the built-in WORKDAY function instead of VBA?
The built-in WORKDAY function adds a specific number of working days to a date. The VBA solution is specifically needed here because the requirement is to add calendar days first (e.g., adding exactly 60 calendar days), and only then adjust the final result backward if it falls on a non-working day.
Why is my VBA function returning a #VALUE! error on the holiday lookup?
This usually happens if the worksheet named 'Holidays' does not exist in your workbook, or if the dates in the holiday list are stored as text instead of valid Date values. Double-check that your holiday tab is named exactly 'Holidays' and that Column B contains valid dates.
How do I modify the VBA code to skip only Sundays?
In your VBA Do While loop, change the weekday condition to check only for vbSunday. Remove the 'Weekday(d) = vbSaturday Or' portion of the code so that the function treats Saturdays as normal business days.
