Calculating Net Working Days excluding Weekends and Holidays

Human resource partners and other professionals must track benefits that arise on a workday basis. Calculating the number of workdays by hand can be tedious and time-consuming, especially when factoring in nonworking days like weekends and holidays. Fortunately, Microsoft Excel’s workday function simplifies this process.

How to Calculate Working Days in Excel and Exclude Any Days You Want

Follow these steps to build your custom calculation.

1. Enter Your Custom Data into the Sheet

Configure two tables. One should be two columns wide and display the starting date and ending date. The other should be one column and include any holidays spanning the analysis period.

2. Choose the Correct Function

Excel has two business day functions — NETWORKDAYS and NETWORKDAYS.intl. The first assumes a traditional Saturday-Sunday weekend and only allows you to enter holiday arguments. NETWORKDAYS.intl includes custom weekend and holiday arguments.

Begin the function by typing the equal sign in the desired output cell and starting to type the function name. Select the correct one.

3. Enter the Starting and Ending Dates

The next line in the helper is “start_date.” Choose it from the table you’ve already added to your worksheet. Click “end_date” and select it from your table before moving on.

4. Add Your Weekend and Holiday Arguments

A pop-up menu should appear when you click on “weekend” in the function-building helper. Scroll to select the correct weekend setup. If you’re working with a conventional weekend and don’t need to add holidays, you can close the bracket and press “Enter.”

To add holidays, click on that option in the helper box after choosing your weekend setup. Highlight the table displaying your holiday dates and close the bracket. The total number of working days, excluding weekends and holidays, should now appear in your output cell.

Customizing Days of the Week for Calculation

Sometimes, you may need to tell Excel which days of the week “count” and which don’t. You’ll customize the “weekend” argument using zeros and ones.

Represent the days you want to count with a zero, beginning with Monday as the first day of the week. Enter a one for all days that you wish to exclude. For example, if you only work Monday, Wednesday, and Friday, you’d enter “0101011.” Surround these numbers with quotation marks for correct syntax. Add holiday arguments next, if necessary.

Learn More About Our Managed IT Services

At Agio, we strive to make technology faster, safer, and simpler to use. Companies across various industries partner with us for cost-effective solutions, ranging from cybersecurity protection to fully managed intelligent IT.

Find out what we can do for you by starting a conversation online.