How to Use Excel NETWORKDAYS.INTL Function?
Excel NETWORKDAYS.INTL Function is used to returns the number of work days between starting dates and the ending dates with inclusion or exclusion of the holidays.
TL;DR
The NETWORKDAYS.INTL function returns the number of working days between two dates, with customizable weekends and optional holidays. The basic syntax is: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]). Use binary codes or numeric values to define weekends (e.g., “0000011” for Saturday-Sunday or 1 for default weekend). Unlike NETWORKDAYS, the .INTL version allows you customize which days are weekends. Find more tutorials at Excel24x7.com.
NETWORKDAYS.INTL Function: A brief
Excel NETWORKDAYS.INTL function is used to calculate the number of working days between the starting dates and the ending dates with configurable holidays date and customizable weekends.
| Feature | NETWORKDAYS | NETWORKDAYS.INTL |
|---|---|---|
| Used For? | Calculates working days between two dates | Calculates working days with custom weekend definitions |
| What about Weekend Days? | Fixed (Saturday & Sunday only) | Fully customizable (can choose any day(s) as weekend) |
| Basic Syntax | =NETWORKDAYS(start_date, end_date, [holidays]) | =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) |
| Holiday Option | Optional list of holidays | Optional list of holidays |
| Weekend Customization Support? | Not supported | Supported via numbers or binary string |
| Can be Used for? | Standard business weeks (Mon–Fri work week) | Non-standard schedules (Sun–Thu work week, rotating shifts) |
| Functions Introduced in? | Excel 2007 | Excel 2010 and later versions. |
NETWORKDAYS.INTL Function: A Syntax
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])- start_date: The starting date of the period.
- end_date: The ending date of the period.
- weekend: A string or number representing which days of the week are considered weekends.
- holidays: A range of dates to exclude as holidays and this is an optional one.
Weekend Codes List supported by NETWORKDAYS.INTL Function:
| Code | Weekend Days |
|---|---|
| 1 | Saturday, Sunday (default) |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| Binary Code | Meaning |
|---|---|
| 0000011 | Saturday & Sunday are weekends |
| 1111111 | All days are weekends (0 workdays) |
| 0000000 | No weekends (every day is a workday) |
| 1000001 | Monday & Sunday are weekends |
| 0101010 | Tuesday, Thursday, Saturday are weekends |
Basic Examples of Using NETWORKDAYS.INTL Function:
In the following example, I have explained how to use the NETWORKDAYS.INTL function to calculate working days with three different scenarios: No weekends, Saturday and Sunday as weekends, and only Sunday as a weekend. I have also used both code formats (Binary and Numerical) to identify holidays. Please take a look.
The Example seems to be simple but it is effective for quick understanding.
| Starting Date | Ending Date | Holidays Dates | Result 1 (No weekends) | Result 2 (Saturday and Sunday as weekends) | Result 3 (Only Sunday as weekend) |
|---|---|---|---|---|---|
| Using Binary Format Weekend Code | |||||
| 02-Jan-24 | 31-Jan-24 | 01-Jan-24 | 30 | 22 | 26 |
| 01-Feb-24 | 29-Feb-24 | 12-Feb-24 | 29 | 21 | 25 |
| Using Numerical Code Format Weekend Code | |||||
| 01-Nov-24 | 30-Nov-24 | 25-Dec-24 | 26 | 21 | 25 |
| 01-Dec-24 | 31-Dec-24 | 01-Jan-25 | 26 | 22 | 27 |
| Formula Used with Binary Code Format: | Result 1: =NETWORKDAYS.INTL(B3,C3,”0000000″) | Result 2: =NETWORKDAYS.INTL(B3,C3,”0000011″,L$3:L$4) | Result 3: =NETWORKDAYS.INTL(B3,C3,”1000000″,L$3:L$4) | ||
| Formula Used with Numeric Code Format: | Result 1: =NETWORKDAYS.INTL(B7,C7,11) | Result 2: =NETWORKDAYS.INTL(B7,C7,1) | Result 3: =NETWORKDAYS.INTL(B7,C7,17) | ||
Explanation of above used Example:
- In this example, the formula =NETWORKDAYS.INTL(B3, C3, “0000011”, L$3:L$4) calculates the number of working days between January 2, 2024 and January 31, 2024, while excluding weekends and holidays.
- The code “0000011” tells Excel that Saturday and Sunday are considered weekends. The holiday list in cells L3:L4 includes January 1, but since it falls outside the date range, only weekends are excluded.
- Since January 2024 has 8 weekend days, Excel subtracts these from the total 31 days of the month, resulting in 22 working days.
How the NETWORKDAYS.INTL Calculated the Working Days?
| Date Period | Weekends Code | Weekends Count | Holidays Count | Workdays |
|---|---|---|---|---|
| Jan 2024 | 0000011 | 8 days | 1 (Jan 1) | 22 |
| Feb 2024 | 0000011 | 8 days | 0 | 21 |
| Nov 2024 | 1 | 8 days | 1 (Nov 25) | 21 |
| Dec 2024 | 17 | 4 days | 0 | 27 |
Is there any Manual Formula Available to Replicate NETWORKDAYS.INTL Functionality?
Yes, there is a manual formula available, you may need to use other Excel functions combined to get the results.
| Scenarios | Manual Formula |
|---|---|
| No Weekends | =C3 - B3 + 1 |
| Saturday and Sunday as Weekends | =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B3&":"&C3)),2)<>6), --(WEEKDAY(ROW(INDIRECT(B3&":"&C3)),2)<>7)) |
| Only Sunday as Weekends | =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B3&":"&C3)),2)<>7)) |
| Function | Used for |
|---|---|
| SUMPRODUCT | Adds up an array of values after performing multiplication or other operations. |
| WEEKDAY | Returns a number for the day of the week. |
| ROW | Returns the row number of a reference. When used on a range of dates, it produces an array of numbers representing each date. |
| INDIRECT | Converts a text string into a valid cell reference |
-- (Double Unary) | Converts TRUE/FALSE results into 1s and 0s, so they can be summed or counted inside SUMPRODUCT. |
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
References:
- NETWORKDAYS.INTL function by Microsoft Support, Retrieved on 26/04/2025: Link.
- NETWORKDAYS function by Microsoft Support, Retrieved on 26/04/2025: Link.
- NETWORKDAYS.INTL by Google Support, Retrieved on 26/04/2025: Link.
- NETWORKDAYS and NETWORKDAYS.INTL functions in Excel by Excel Olympics, Retrieved on 26/04/2025: Link.
- NETWORKDAYS.INTL by Libreoffice, Retrieved on 26/04/2025: Link.
- Calculate Working/Business Days Between 2 Dates Using NETWORKDAYS and NETWORKDAYS.INTL by Mike Thomas, Retrieved on 26/04/2025: Link.
- What is the difference between Networkdays and Networkdays INTL? by Quora, Retrieved on 26/04/2025: Link.
Recently Published Tutorials:
