|
Getting your Trinity Audio player ready...
|
When you’re managing project timelines, HR attendance, or payroll schedules, calculating working days between two dates — excluding weekends and holidays — is a common need.
Instead of manually counting, Excel gives us two powerful functions for this:
NETWORKDAYS() and NETWORKDAYS.INTL().
In this guide, we’ll learn both functions with real-life examples, a holiday table, and the difference between numeric and text weekend formats in NETWORKDAYS.INTL.
What is NETWORKDAYS Function in Excel?
NETWORKDAYS() returns the number of working days between two dates, automatically excluding weekends (Saturday & Sunday by default) and any optional holidays you specify.
Syntax
NETWORKDAYS(start_date, end_date, [holidays])
Parameters
- start_date – The start of the date range.
- end_date – The end of the date range.
- holidays (optional) – A range or array of holiday dates to exclude.
Example 1: Basic Working Days Between Two Dates
Suppose you want to calculate how many working days are between January 1, 2025, and January 31, 2025.
=NETWORKDAYS("1-Jan-2025", "31-Jan-2025")
✅ Result: 23 working days
Excel automatically skips Saturdays and Sundays.
Adding Holidays to NETWORKDAYS
Now, let’s exclude official holidays from your working day count.
Holiday List for 2025
| Date | Day | Holiday | Type |
|---|---|---|---|
| Jan 26 | Sunday | Republic Day | National |
| Feb 26 | Wednesday | Maha Shivaratri | Public |
| Mar 14 | Friday | Holi | Public |
| Mar 31 | Monday | Eid al-Fitr | Public |
| Apr 10 | Thursday | Mahavir Jayanti | Public |
| Apr 18 | Friday | Good Friday | Public |
| May 12 | Monday | Buddha Purnima | Public |
| Jun 7 | Saturday | Bakrid / Eid al-Adha | Public |
| Jul 6 | Sunday | Muharram | Public |
| Aug 15 | Friday | Independence Day | National |
| Aug 16 | Saturday | Janmashtami | Public |
| Sep 5 | Friday | Eid e Milad | Public |
| Oct 2 | Thursday | Gandhi Jayanti & Dussehra | National / Public |
| Oct 21 | Tuesday | Diwali | Public |
| Nov 5 | Wednesday | Guru Nanak Jayanti | Public |
| Dec 25 | Thursday | Christmas | Public |
(Assume these holidays are in cells H2:H17)
Example 2: Exclude Holidays from Working Days
=NETWORKDAYS("1-Mar-2025","31-Mar-2025", H2:H17)
✅ This formula excludes weekends and any holiday listed in H2:H17.
Result: 21 working days (depending on the exact dates).
The Limitation of NETWORKDAYS
The NETWORKDAYS() function assumes Saturday and Sunday are weekends.
But in reality:
- Some companies have Sunday only off.
- Some countries follow Friday–Saturday weekends.
- Some have custom weekend schedules (like retail or customer support teams).
For that flexibility, we use NETWORKDAYS.INTL().
NETWORKDAYS.INTL Function in Excel
NETWORKDAYS.INTL() lets you define custom weekends and still exclude holidays.
Syntax
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Parameters
- start_date – Start of the date range.
- end_date – End of the date range.
- weekend (optional) – Defines which days are weekends.
- holidays (optional) – A range of dates to exclude.
Example 3: Default (Saturday & Sunday Off)
This works the same as the basic NETWORKDAYS().
=NETWORKDAYS.INTL("1-Jan-2025","31-Jan-2025",1)
✅ Result: 23 working days
Numeric Codes for Weekends in NETWORKDAYS.INTL
You can define weekends using a numeric code (1–17).
| 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 |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Example 4: Sunday Only Weekend
If your company only observes Sunday as a weekend:
=NETWORKDAYS.INTL("1-Feb-2025","28-Feb-2025",11, H2:H17)
✅ This counts all days except Sundays and the listed holidays.
Example 5: Friday–Saturday Weekend
=NETWORKDAYS.INTL("1-May-2025","31-May-2025",7,H2:H17)
✅ Friday and Saturday are treated as weekends.
Example 6: Monday Only Weekend
=NETWORKDAYS.INTL("1-Jun-2025","30-Jun-2025",12,H2:H17)
✅ Excludes all Mondays + holidays.
Text Format for Weekends (7-Character String)
Instead of numeric codes, you can use a 7-character string to represent weekends.
Each character corresponds to a day starting from Monday → Sunday.
0= Working day1= Weekend
| String | Weekend Days |
|---|---|
"0000011" | Saturday & Sunday |
"0001100" | Friday & Saturday |
"0000001" | Sunday only |
"1000000" | Monday only |
"0010000" | Wednesday only |
Example 7: Saturday & Sunday (Text Format)
=NETWORKDAYS.INTL("1-Apr-2025","30-Apr-2025","0000011",H2:H17)
✅ Same as numeric code 1.
Example 8: Sunday Only Weekend
=NETWORKDAYS.INTL("1-Jul-2025","31-Jul-2025","0000001",H2:H17)
✅ Same as numeric code 11.
Example 9: Friday & Saturday Weekend
=NETWORKDAYS.INTL("1-Aug-2025","31-Aug-2025","0001100",H2:H17)
✅ Equivalent to numeric code 7.
Example 10: Only Wednesday Off
=NETWORKDAYS.INTL("1-Sep-2025","30-Sep-2025","0010000",H2:H17)
✅ This excludes only Wednesdays and holidays.
Tip: Combine with TODAY()
To calculate how many working days remain until today, use:
=NETWORKDAYS.INTL(TODAY(), "31-Dec-2025", "0000011", H2:H17)
This dynamically updates every day.
Comparing NETWORKDAYS vs NETWORKDAYS.INTL
| Feature | NETWORKDAYS | NETWORKDAYS.INTL |
|---|---|---|
| Default weekend | Saturday & Sunday | Saturday & Sunday |
| Custom weekends | ❌ Not supported | ✅ Fully supported |
| Text-based weekend | ❌ | ✅ Yes ("0000011") |
| Region flexibility | Limited | High |
| Best for | Simple standard workweek | Global or flexible schedules |
Real-Life Scenarios
Scenario 1: HR Attendance Report
HR wants to know how many working days each employee attended in a month excluding weekends and public holidays.
Formula (for each employee):
=NETWORKDAYS.INTL(StartDate, EndDate, "0000011", Holidays)
You can use this along with COUNTIF() to compare attendance logs.
Note: COUNTIF & COUNTIFS in Excel
Scenario 2: Project Deadline Calculation
You’re planning a project starting on 10-Feb-2025 and need to finish in 40 working days.
To find the end date, use WORKDAY.INTL():
=WORKDAY.INTL("10-Feb-2025",40,"0000011",H2:H17)
This gives the completion date excluding weekends and holidays.
Scenario 3: Banking Operations (Sunday Only Off)
Banks often work six days a week with only Sunday off.
=NETWORKDAYS.INTL("1-Mar-2025","31-Mar-2025","0000001",H2:H17)
This gives total working days in March excluding Sundays and holidays.
Scenario 4: Global Team with Friday–Saturday Off
If your Middle East branch observes Friday–Saturday weekends:
=NETWORKDAYS.INTL("1-Jun-2025","30-Jun-2025","0000110",H2:H17)
Use a custom string to maintain accurate workday calculations per region.
Troubleshooting Common Errors
| Error | Reason | Fix |
|---|---|---|
#VALUE! | Invalid date format | Use valid Excel date cells, not text |
| Wrong working day count | Weekend argument incorrect | Verify 7-character string or numeric code |
| Holidays not excluded | Holiday range not properly referenced | Use absolute range $H$2:$H$17 |
Bonus: Custom Weekend Patterns You Can Use
| Weekend Pattern | String Format | Use Case |
|---|---|---|
| Saturday & Sunday | "0000011" | Default |
| Sunday only | "0000001" | Retail, banking |
| Friday & Saturday | "0001100" | Middle East countries |
| Wednesday only | "0010000" | Custom shift teams |
| Friday only | "0000100" | Special operation |
Summary
- NETWORKDAYS() – Quick calculation between two dates (Sat–Sun off).
- NETWORKDAYS.INTL() – More flexible, lets you define any custom weekend.
- Use holiday range to exclude official days off.
- Numeric weekend codes are short, but text strings (like
"0000011") are clearer and universal. - Combine with TODAY() or WORKDAY.INTL() for dynamic schedules.
Example for Practice
Try this formula in Excel:
=NETWORKDAYS.INTL(DATE(2025,1,1), DATE(2025,12,31), "0000001", $H$2:$H$17)
It will show total working days in 2025, excluding Sundays and official holidays.
Final Thoughts
When you’re managing MIS, HR, project timelines, or finance reports, you’ll often need to compute accurate working days.
By mastering both NETWORKDAYS() and NETWORKDAYS.INTL(), you can automate those calculations precisely, even for global or shift-based teams.
Once you understand the logic of numeric and text weekend codes, you’ll have complete flexibility — whether your office works 5 days, 6 days, or custom rotating weekends.
What’s Next?
In the next post, we’ll learn about the Flash Fill in Excel