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
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;
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;
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;
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.