VBA & Macro Problems

How to Calculate Working Days Excluding Holidays in Excel (VBA & Formula)

Khadija Khan Oct 23, 2026 869 views

Question details

The user needs to calculate the total number of working days between a start date and an end date while excluding weekends and a custom list of holidays, either via a VBA UserForm or directly on the worksheet.

How to Calculate Working Days Excluding Holidays in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up an automated date calculator for project management, payroll, or scheduling to find exact business days without manually counting.
Observed behavior: A reliable method is required to process start and end dates against a defined schedule and custom holiday list to output the net working days.

Before you start

Before beginning your calculations, prepare a list of your specific regional or company holidays in a blank column on your worksheet, as Excel does not track holidays automatically.

Solution 1Recommended

Calculate Working Days Using the NETWORKDAYS.INTL Formula

The most straightforward approach is using Excel's built-in NETWORKDAYS.INTL function directly on the worksheet, which calculates net workdays and allows customizable weekend parameters.

This function is highly versatile because the 'weekend' argument lets you specify exactly which days of the week should be considered non-working days. A weekend code of '1' excludes Saturday and Sunday.

Prepare your dates

In your worksheet, enter your start date in cell A2 and your end date in cell B2. Create a list of holiday dates in a separate range, for example, D2:D10.

Enter the formula

Select the cell where you want the result to appear (e.g., C2). Type the formula: =NETWORKDAYS.INTL(A2, B2, 1, D2:D10).

Calculate the result

Press the Enter key. Excel will process the dates and display the total number of working days, having successfully excluded both the weekends and your custom holidays.

Calculate Working Days Using the NETWORKDAYS.INTL Formula

Customizing the Weekend: If your workweek is different, you can change the '1' to another code (e.g., '11' for Sunday only, or '2' for Sunday and Monday) as defined by Microsoft's documentation.

Solution 2

Create a VBA UserForm to Calculate Working Days

For advanced data entry or automated dashboards, you can build a VBA UserForm that collects the start and end dates and uses WorksheetFunction to return the working days.

Seamless Date Calculations

Easily Calculate Working Days with WPS Spreadsheet

WPS Spreadsheet fully supports advanced date functions like NETWORKDAYS.INTL and advanced VBA Macros, allowing you to seamlessly build UserForms, calculate working days, and manage project schedules with zero compatibility issues.

1. Open WPS Spreadsheet: Launch WPS Spreadsheet and input your start date, end date, and holiday list into the grid.
2. Select target cell: Click on the specific cell where you want to output the total working days.
3. Apply the function: Type =NETWORKDAYS.INTL(start_date, end_date, 1, holidays) using your specific cell references.
4. Get instant results: Press Enter to instantly calculate the precise working days excluding the specified non-working days.

100% compatible with Microsoft Excel formulas like NETWORKDAYS.INTLFull support for VBA UserForms and macros in the Pro versionFamiliar user interface requiring no learning curveLightweight application that loads faster for complex calculations

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the weekend days in the NETWORKDAYS.INTL formula?

The third argument in the function controls the weekend days. Use 1 for Saturday and Sunday, 2 for Sunday and Monday, 7 for Friday and Saturday, or 11 for Sunday only. Check the formula tooltips for the full list of codes.

Can I calculate working days without excluding holidays?

Yes, simply omit the final 'holidays' argument in the formula. You can write it as =NETWORKDAYS.INTL(start_date, end_date, 1) and it will only exclude the standard weekends.

Why does my date calculation return a #NAME? error?

This error typically occurs if the function name is misspelled or if you are using an older version of Excel that does not support the .INTL extension. Double-check that you typed NETWORKDAYS.INTL exactly.

Does the formula include the start and end dates in the final count?

Yes, if the start date or end date falls on a valid working day, it is included in the total count. It calculates the inclusive duration between the two dates.

Excel & Data Problems