The Excel NETWORKDAYS.INTL Function is used to return the number of work days between the starting dates and the ending dates, with the inclusion or exclusion of the holidays.
NETWORKDAYS.INTL Function: A Brief Intro
The Excel NETWORKDAYS.INTL function is used to calculate the number of working days between the starting dates and the ending dates with configurable holiday dates 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 a 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.
- weekendekend: 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 |
Code |
|---|---|
|
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, and 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.
Explanation of the above 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 Calculates the Working Days
Is There any Manual Formula Available to Replicate the NETWORKDAYS.INTL Functionality?
Yes, there is a manual formula available; you may need to use other Excel functions combined to get the results.
Scenarios
That’s it. This article was originally published on How to Use Excel NETWORKDAYS.INTL Function?