Using NETWORKDAYS.INTL Function in Excel
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.
Visual Representation of Difference b/w NETWORKDAYS & NETWORKDAYS.INTL Function
| 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. |
Difference Between NETWORKDAYS & NETWORKDAYS.INTL Function
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 |
Numerical Weekend Codes List for NETWORKDAYS.INTL Function
| 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 |
7 Digit Binary Weekend Code format for NETWORKDAYS.INTL Function
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.
Clear Examples of Finding number of Working days with customized holidays using NETWORKDAYS.INTL Function
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)) |
Manual Formula Examples
| 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.
