How to Get Workdays Between Dates in Excel

Why This Task Matters in Excel

In every organization, time equals money, and the most universally tracked unit of time is the working day. Payroll departments calculate wages based on days worked, project managers estimate delivery schedules by counting business days, and accountants assess penalties or discounts that apply only on workdays. If you miscalculate those workdays, employees might be underpaid, delivery promises can be missed, and finance teams could report inaccurate accruals. That quickly snowballs into compliance issues, customer dissatisfaction, or costly rework.

Consider a manufacturing firm that promises shipment “within five business days.” The definition of “five business days” varies across countries, and the presence of public holidays further complicates the count. A shipping department that equates calendar days to workdays risks failing its service-level agreement. HR teams face similar stakes: onboarding paperwork or background checks often have regulatory time frames measured in business days. Miscounts can make the company non-compliant.

Excel is the de-facto tool for ad-hoc calculations because it is ubiquitous, instantly recalculates when assumptions change, and integrates with other Office applications. Functions such as NETWORKDAYS and NETWORKDAYS.INTL let you compute business-day intervals quickly and accurately, even when weekends differ by country or when there is a custom corporate holiday calendar. When you learn to count workdays correctly, you elevate many other workflows—project Gantt charts, invoice due-date schedules, capacity planning models, and resource forecasts. Mastering this task is foundational; without it, every derivative schedule or cost estimate you build on top of that date math is at risk of being wrong.

Best Excel Approach

Although several workarounds exist, the purpose-built NETWORKDAYS family of functions is the most reliable way to count working days. The original NETWORKDAYS function—part of Excel’s Analysis ToolPak and built-in since Excel 2007—counts the number of workdays between two dates, automatically excluding Saturdays, Sundays, and any optional holiday list you provide. For organizations that operate on non-standard weekends, the later NETWORKDAYS.INTL offers full weekend customization plus a pattern code for complex schedules.

Choose NETWORKDAYS if your weekends are Saturday–Sunday and you only need one holiday list. Opt for NETWORKDAYS.INTL when you have alternate weekends—Friday–Saturday in many Middle-East countries, or even a single-day weekend. Both functions are lightweight, require no helper columns, and recalculate instantly, making them perfect for dynamic models.

Syntax for the recommended approach (standard Western weekend):

=NETWORKDAYS(start_date, end_date, [holidays])

Custom weekend version:

=NETWORKDAYS.INTL(start_date, end_date, weekend_code, [holidays])

Parameters

• start_date – the first date in the range (inclusive)
• end_date – the last date in the range (inclusive)
• weekend_code – a numeric or text code defining which days are non-working
• holidays – an optional range containing company or public holidays to exclude

Parameters and Inputs

Both functions require valid Excel dates, which are serial numbers starting at 1-Jan-1900 on Windows or 1-Jan-1904 on Mac. Enter dates with DATE(year,month,day) or as text that Excel recognizes, then apply Date formatting to guarantee consistency. The start_date and end_date can be cell references, hard-coded serials, or results of other formulas.

The optional holidays argument accepts either a contiguous range like [G2:G15] or a literal array inside a formula such as DATE(2023,1,1). Holiday cells must contain proper dates; blank cells are ignored. If the holiday list sits on another worksheet, use a fully qualified reference like \'HolidayCalendar\'![A2:A30].

For NETWORKDAYS.INTL, weekend_code can be:

• A numeric code (1 through 17) where 1 = Saturday-Sunday, 2 = Sunday-Monday, etc.
• A 7-character text string such as \"0000011\", where each character represents Monday through Sunday (1 = non-working, 0 = working). This allows highly specific patterns like a single-day weekend on Friday.

Input validation tips: confirm that end_date is not earlier than start_date; otherwise the result is negative. When dates originate from imported CSV files, multiply by 1 or wrap in DATEVALUE to coerce them into proper serials. If you anticipate empty inputs, wrap the formula in IF or LET logic to handle blanks gracefully.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose you have a customer contract that begins on 3-Apr-2023 and ends on 14-Apr-2023. You want to know how many business days will be billed.

Sample data in [A2:B3]
A2: “Start Date” B2: 3-Apr-2023
A3: “End Date”  B3: 14-Apr-2023

Step 1 – Enter the formula in C2:

=NETWORKDAYS(B2,B3)

Step 2 – Press Enter. Excel returns 10. Here is the reasoning: The function counts both start and end dates if they are workdays. Between 3-Apr and 14-Apr 2023 there are two Saturdays and two Sundays, so 12 calendar days minus four weekend days equal eight. Because both Monday 3-Apr and Friday 14-Apr are workdays, they get counted, bringing the total to ten.

Screenshot description: The worksheet displays three columns—labels in column A, dates in column B, and the result in column C reading “10” with General formatting.

Common variations: If you want to exclude the start date (e.g., you bill from the day after the contract starts), subtract one from the result or shift start_date by one: =NETWORKDAYS(B2+1,B3). Troubleshooting tip: If you see “#VALUE!”, confirm that cells B2 and B3 are formatted as Date or Number. Text dates that Excel fails to recognize cause errors.

Example 2: Real-World Application

Imagine a project manager scheduling a construction project in Dubai, where the weekend is Friday–Saturday, not Saturday–Sunday, and the local holiday of Eid al-Fitr falls on 21-Apr-2023. The project runs from 16-Apr-2023 to 27-Apr-2023.

Data setup

• B5: 16-Apr-2023 (start)
• B6: 27-Apr-2023 (end)
• G2:G3: Holiday list with 21-Apr-2023

Weekend customization: Friday–Saturday is weekend_code 7 in the numeric scheme.

Step 1 – Create the formula:

=NETWORKDAYS.INTL(B5,B6,7,G2:G3)

Step 2 – The result is 8. Explanation: Between 16-Apr and 27-Apr inclusive, there are twelve calendar days. Fridays and Saturdays (four days total) are excluded, leaving eight potential workdays. Eid al-Fitr is on a Friday, already removed, so the holiday list does not further reduce the count.

Business context: The contractor uses this eight-day count to estimate labor cost and rent equipment for exactly the number of active workdays. Integration: They link this formula to a pivot table summarizing daily work packages, so if the schedule shifts, dependencies adjust automatically. Performance note: Even in large projects with thousands of line items, NETWORKDAYS.INTL calculates rapidly because it only evaluates contiguous holiday ranges once per recalc.

Example 3: Advanced Technique

A multinational team rotates shifts where Sunday is a half-day counted as 0.5 workday, and Saturday is completely off. The schedule spans 1-Jan-2023 through 31-Jan-2023, and there is a corporate holiday on 16-Jan-2023. NETWORKDAYS.INTL cannot natively count fractional workdays, so combine it with an adjustment.

Step 1 – Calculate full workdays excluding standard weekend (Saturday-Sunday):

=NETWORKDAYS.INTL(B10,B11,1,G10:G10)

Assume B10 holds 1-Jan-2023, B11 holds 31-Jan-2023, and G10 lists 16-Jan-2023. This returns 21.

Step 2 – Count Sundays (half-days):

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B10&":"&B11)))=1))

Explanation: WEEKDAY(...)=1 identifies Sundays. SUMPRODUCT tallies them. The result for January 2023 is 5.

Step 3 – Combine for fractional workdays:

=NETWORKDAYS.INTL(B10,B11,1,G10:G10) + (Sundays*0.5)

Where Sundays is the formula result from Step 2. Final value: 21 + 2.5 = 23.5 workday equivalents.

Optimization tip: Wrap repeating calculations inside LET to compute start and end serials once. Error handling: If your date span crosses month boundaries, make INDIRECT resilient by testing for end_date earlier than start_date. Professional insight: For exceptionally large spans (multiple years), generate a dynamic array of dates with SEQUENCE instead of INDIRECT to avoid volatile functions.

Tips and Best Practices

1. Maintain a dedicated “Holidays” sheet and name the range Holidays. This lets everyone reuse the same list by referencing Holidays in formulas.
2. Use NETWORKDAYS.INTL even for standard Saturday–Sunday weekends; it keeps your model flexible if your business expands into markets with different weekends.
3. Document weekend_code choices alongside formulas in a comment or helper cell so successors know why code 17 appears.
4. Store start and end dates as date serials, not text, to eliminate regional ambiguity (4/5/23 means 4-May in some locales).
5. Test edge cases by placing sample start or end dates on known holidays; verify that your holiday list correctly excludes them.
6. For dashboards, convert results into dynamic array outputs to feed into charts that visualize workdays per month without manual refresh.

Common Mistakes to Avoid

1. Mixed date formats – Importing CSV dates as text can cause NETWORKDAYS to return #VALUE!. Use DATEVALUE or multiply by 1 to coerce.
2. Empty holiday cells inside the range – Blank cells in the holiday argument are counted as zero, not ignored. Clean the range or use FILTER.
3. Start date after end date – NETWORKDAYS returns negative counts, often unnoticed in hidden columns. Wrap with ABS or a validation check.
4. Hard-coding weekend_code – If your organization changes its weekend policy, buried constants break quietly. Reference a parameter cell instead.
5. Using NETWORKDAYS instead of NETWORKDAYS.INTL for global schedules – Assumes Saturday–Sunday weekends, leading to significant miscounts in regions where that is false.

Alternative Methods

Sometimes you cannot rely on NETWORKDAYS—perhaps you are sharing a workbook with users on pre-2007 Excel, or you need per-row weekday logic beyond what the functions provide.

SUMPRODUCT pattern:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)),2)<=5))

This counts weekdays 1-5 under WEEKDAY(...,2). Add a holiday exclusion by subtracting another SUMPRODUCT that tests membership in the holiday list. Power Query alternative: Load dates into a query, perform a Date.IsInIsoWeekday column, filter out weekends and holidays, then aggregate.

FAQ

When should I use this approach?

Use NETWORKDAYS or NETWORKDAYS.INTL when you need an immediate, formula-based count of business days that automatically updates if dates shift, especially for schedules, KPIs, or financial models.

Can this work across multiple sheets?

Yes. Reference start_date and end_date on any sheet:

=NETWORKDAYS('Input Sheet'!B2,'Input Sheet'!B3,Holidays)

Ensure the holiday named range is workbook-level, not sheet-level, for global accessibility.

What are the limitations?

The functions assume full-day granularity; they cannot natively handle half-days or variable daily hours. They also treat weekends uniformly across the entire date span; split-weekend scenarios require workaround logic.

How do I handle errors?

Wrap formulas in IFERROR to trap #VALUE! results, or use data validation to block non-date entries. For negative outputs, add an IF test that warns users when end_date precedes start_date.

Does this work in older Excel versions?

NETWORKDAYS exists as an add-in in Excel 2003 (Analysis ToolPak). NETWORKDAYS.INTL is available only from Excel 2010 onward. For pre-2010 users, resort to NETWORKDAYS or custom SUMPRODUCT logic.

What about performance with large datasets?

NETWORKDAYS scales well because it evaluates once per row. Problems appear only when INDIRECT or volatile functions are used. If you must count workdays across millions of rows, consider off-loading counting to Power Query or a database engine, then import the summarized result.

Conclusion

Counting workdays correctly is foundational to accurate scheduling, cost estimation, and compliance reporting. Excel’s NETWORKDAYS and NETWORKDAYS.INTL give you robust, flexible, and internationally adaptable tools that integrate smoothly with every other Excel feature, from pivot tables to dynamic arrays. By mastering the techniques covered here—basic counting, alternate weekends, fractional adjustments, and troubleshooting—you equip yourself to build reliable models and meet real-world business demands. Continue exploring related skills such as WORKDAY for forward date calculation and Power Query for large-scale transformations to round out your date-handling expertise.