Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Excel NETWORKDAYS.INTL Function

In Excel, when we need to calculate business days or workdays between two dates, excluding weekends and holidays, we can use the NETWORKDAYS and NETWORKDAYS.INTL functions. But with the NETWORKDAYS.INTL function we have additional choice to use the custom weekend parameter and set a custom weekday(s) as weekend(s). This article will step through how.

Figure 1. How to Use The Excel NETWORKDAYS.INTL Function

Syntax

The generic syntax for the NETWORKDAYS.INTL function is;

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

The start_date and end_date arguments are required and are entered as cell reference or the DATE function, but weekend and holidays arguments are optional. The weekend argument is set to Saturday and Sunday as default if we omit this argument. But, we also have a choice to set custom weekday(s) as weekend(s) with custom weekend parameters using Index Number or weekend string.

Figure 2. The Syntax for the NETWORKDAYS.INTL Function

Custom Weekend Parameters

The custom weekend parameter determines which day(s) of the week should be considered as weekend(s) and we can set this parameter by using index Number or Weekend String.

  • Number It is an index number assigned to each weekday or set of weekdays as weekend(s) given in figure 3 below.
  • Weekend String – It is a series of seven 1’s and 0’s representing seven days of the week beginning with Monday. A custom weekend day is chosen by entering 1 and the working day is represented by 0, such as;
    “0000011” – Saturday and Sunday are weekends
    “0000110” – Friday and Saturday are weekends
    “0000001” – Only Sunday is weekend

Figure 3. The Custom Weekend Parameter Numbers

Calculating Workdays Excluding Default Weekends and Holidays

To calculate business days or workdays between two dates, excluding Saturday and Sundays as default weekends and holidays, listed in the range B7: B9, we need to enter the following formula in cell D2 as per our example;

=NETWORKDAYS.INTL(B2,C2,1,$B$7:$B$9)

Here, we need to either enter 1 or omit the weekend argument to set the Saturday and Sunday as default weekends.

Figure 4. Calculating Workdays – Excluding Default Weekends and Holidays

Calculating Workdays Excluding Custom Weekend(s) Only

Using custom weekend parameter we can set the custom weekday(s) as weekend(s) in the NETWORKDAYS.INTL function, and can calculate the workdays between dates excluding custom weekends(s) only. In this case, we will enter the following formula in cell D3 by setting Sunday only as the weekend to calculate workdays;

=NETWORKDAYS.INTL(B3,C3,11)

Figure 5. Calculating Workdays – Excluding Custom Weekend(s) Only

Calculating Workdays Excluding Custom Weekends and Holidays

Excluding custom weekends (Sunday) and holidays (B7: B9), the following NETWORKDAYS.INTL function will be used in cell D4 to calculate workdays between dates as per our example;

=NETWORKDAYS.INTL(B4,C4,11,$B$7:$B$9)

Figure 6. Calculating Workdays – Excluding Custom Weekends and Holidays

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I have multiple columns on my spread sheet and want to switch from A-Z to Z-A based on values in column A
Solved by V. H. in 38 mins
Concat formula is incorrectly formatting the date in column C. Currently column B has dd/mm/yyyy and I require mm/dd/yyyy
Solved by Z. Y. in 31 mins
I need a formula that will allow me to calc before a certain date and after a certain date. =if(A2<=DATE(2015,8,27),(F2*1.14),(F2*1.15)) It just multiplies column F with 1.15. I'm assuming I need to bring in the date column into the equation?
Solved by M. Q. in 20 mins
I need assistance setting up a time sheet formula where I can create a column of contiguous dates down the left hand side of the spread sheet. So I can copy a contiguous column of dates, rather than entering each date manually. I would only need to have work days in the list, ie Monday to Friday inc. with a couple of spare lines between each week.
Solved by Z. Y. in 40 mins
Example: If a loan closes between October 1st & 15th, the first mortgage payment is due November 1st. If it closes between October 16th & 31st, the first payment is due December 1st.
Solved by S. S. in 15 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc