How to List Workdays Between Dates in Excel

Why This Task Matters in Excel

In everyday business, almost every schedule, forecast, or compliance document revolves around working days rather than calendar days. Payroll officers need to know how many working days fall in a pay period to prorate salaries for new hires. Project managers produce Gantt charts that skip weekends so deliverables line up with real office days. Procurement teams often issue purchase orders that specify delivery within “10 business days” rather than “two calendar weeks.” These are not edge cases—they are core, recurring tasks in finance, operations, logistics, and HR.

Excel remains the most widely used tool for such day-based planning because it combines raw calculation power with the ability to present schedules visually. If you can automatically list all workdays between two dates, you can feed that list into summary formulas, conditional formatting, or even chart timelines without manual editing. This reduces typing errors, improves consistency, and enables flexible scenario analysis (e.g., “What if we start next Monday instead of Wednesday?”).

Ignoring the distinction between calendar days and workdays can cause material consequences: overstated revenue recognition, late penalty fees on missed milestones, or inaccurate staffing plans that leave teams idle. Beyond avoiding mistakes, generating a clean list of business days lets you link into other skills—NETWORKDAYS to count them, XLOOKUP to assign resources, or SUMIFS to add production output by day. In short, mastering this task strengthens both basic time-series analysis and high-level planning capabilities in Excel.

Best Excel Approach

The most robust way to generate a live list of workdays is a dynamic-array formula that combines SEQUENCE with WORKDAY (or WORKDAY.INTL for custom weekends) and spills the result downward automatically. This approach is ideal because:

• It needs only one cell—Excel generates the rest of the list.
• It recalculates instantly if start date, end date, or holiday list changes.
• It works in Microsoft 365, Excel 2021, and Excel for the web without VBA.

Core logic:

1. Count how many workdays exist between the two boundary dates using NETWORKDAYS (or NETWORKDAYS.INTL).
2. Generate that many sequential numbers with SEQUENCE.
3. Feed each incremental value into WORKDAY so each row returns the next valid business day.

Recommended formula (standard Monday-Friday workweek, optional holiday list in [Holidays]):

=WORKDAY(StartDate-1,SEQUENCE(NETWORKDAYS(StartDate,EndDate,Holidays)),Holidays)

Alternative for regions with different weekends (e.g., Friday-Saturday):

=WORKDAY.INTL(StartDate-1,SEQUENCE(NETWORKDAYS.INTL(StartDate,EndDate,"0000110",Holidays)),"0000110",Holidays)

Why subtract 1 from StartDate? It ensures WORKDAY counts the first returned date as the actual start date if it is itself a workday.

Use this dynamic array wherever possible; resort to legacy helper columns or Power Query only when your Excel version lacks dynamic arrays or when a static output (no formulas) is required.

Parameters and Inputs

To make the solution robust, prepare these inputs:

• StartDate – A valid Excel date (serial number) or reference like [B2].
• EndDate – Another valid date equal to or after StartDate.
• Holidays – Optional one-column range such as [H2:H15] listing non-working dates; must use real date values, not text.
• Weekend Pattern – For WORKDAY.INTL or NETWORKDAYS.INTL, a seven-character text string where \"1\" marks weekend days, \"0\" marks workdays; the pattern starts on Monday.
• Validation – Ensure StartDate ≤ EndDate; you can add Data Validation rules or wrap formula in IFERROR to handle reversed dates.
• Data Type Consistency – All date cells should use Number format Date; text dates are prone to regional misinterpretation.
• Edge Cases – Start or End date falling on a weekend or holiday is handled automatically by WORKDAY functions, but verify holiday list is updated yearly to avoid omissions.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a simple staffing plan where onboarding starts on 03-Apr-2023 and ends on 14-Apr-2023. You want a daily checklist only for workdays.

1. Enter 03-Apr-2023 in [B2] and 14-Apr-2023 in [B3].
2. Leave holidays blank for now.
3. In [B5] type the dynamic array formula:

=WORKDAY(B2-1,SEQUENCE(NETWORKDAYS(B2,B3)),)

4. Press Enter. Excel 365 spills five dates: 03-Apr, 04-Apr, 05-Apr, 06-Apr, 10-Apr (Good Friday and weekend skipped).
5. Apply a custom date format \"ddd dd-mmm\" for clarity.

Why it works: NETWORKDAYS counts business days (5 here). SEQUENCE creates [1,2,3,4,5]. WORKDAY advances from B2-1 by each number, ignoring weekends.

Common variations:

• Need just count of days? Use NETWORKDAYS alone.
• Want the list horizontally? Add a second SEQUENCE argument: SEQUENCE(count,1) becomes SEQUENCE(1,count).

Troubleshooting tips: If your offices work on Saturdays, switch to WORKDAY.INTL with pattern \"0000001\", meaning only Sunday is weekend.

Example 2: Real-World Application

A construction company must create a delivery log for a six-month project. Any shipments cannot arrive on corporate holidays stored in [H2:H12], and the site is closed on Sundays only.

1. Place project start 15-Jun-2023 in [C2] and project end 15-Dec-2023 in [C3].
2. Enter holiday dates (Independence Day, Labor Day, Thanksgiving week, etc.) in [H2:H12].
3. In [C5] enter:

=WORKDAY.INTL(C2-1,
              SEQUENCE(NETWORKDAYS.INTL(C2,C3,"0000001",Holidays),"0000001",Holidays),
              "0000001",
              Holidays)

Because NETWORKDAYS.INTL and WORKDAY.INTL both need the weekend code, use \"0000001\" so only Sunday acts as weekend.

4. Copy the spilled list into a separate column named DeliveryCalendar and hand it to the logistics team.
5. The team adds a VLOOKUP to assign truck numbers—a demonstration of how the dynamic list integrates with other features.

Performance note: Over six months this list contains roughly 156 rows. Excel arrays handle thousands effortlessly, but if you expect 50,000 rows, consider converting to a static range via Copy > Paste Values after finalization.

Example 3: Advanced Technique

Suppose a multinational support team works alternating weekends: Week 1 has Saturday off, Week 2 has Sunday off, repeating. Official holidays differ by country. We will handle:

• Variable weekend pattern via a custom helper function using LAMBDA (Excel 365).
• Separate holiday lists per region merged with VSTACK.
• Dynamic spill into structured table for Power Pivot.

Step-by-step:

1. Create a named range WeekPattern in the Name Manager with this LAMBDA:

=LAMBDA(date, IF(ISODD(WEEKNUM(date)), "0000010", "0000001"))

It returns \"Saturday off\" for odd weeks and \"Sunday off\" for even weeks.

2. In [G2:G50] list US holidays; [H2:H30] list UK holidays. Combine them with:

=UNIQUE(VSTACK(G2:G50,H2:H30))

Name this range HolidaysGlobal.

3. Enter StartDate 01-Jan-2024 in [D2] and EndDate 31-Mar-2024 in [D3].

4. Insert this array in [D5]:

=LET(
    d0, D2-1,
    days, SEQUENCE(EndDate-StartDate+1),
    workPattern, MAP(StartDate+days-1, WeekPattern),
    validDays, FILTER(StartDate+days-1, NETWORKDAYS.INTL(StartDate+days-1, StartDate+days-1, workPattern, HolidaysGlobal)=1),
    validDays)

Explanation:

• SEQUENCE builds every date between boundaries.
• MAP creates the individualized weekend pattern per date.
• NETWORKDAYS.INTL returns 1 only if that single date is a workday under its unique pattern.
• FILTER keeps only those dates.

Professional tips:

• LET reduces repetitive calculations.
• MAP requires Excel 365; if unavailable, pivot to Power Query custom column logic.
• Store the result in an Excel Table for easier downstream relationships in Power Pivot.

Tips and Best Practices

1. Name Your Inputs – Convert [B2:B3] into named ranges StartDate, EndDate; formulas become readable and less error-prone.
2. Separate Config from Logic – Keep weekend patterns and holiday lists on a dedicated “Config” sheet so year-to-year updates do not touch formulas.
3. Use LET for Clarity – When formulas grow complex, LET allows variable assignments that document each interim result and boost performance.
4. Format Results as Table – Convert spilled range to an Excel Table (Ctrl + T) to gain automatic expansion and easy reference in other formulas (e.g., Table1[Workday]).
5. Store Holidays as Dates, Not Text – Always verify with ISTEXT; if TRUE, convert via DATEVALUE to avoid silent failures.
6. Archive Yearly – At fiscal year-end, copy the final schedule and paste as values for historical records, then refresh formulas for the new year.

Common Mistakes to Avoid

1. Using Text Dates – Typing “1/2/23” in a cell formatted as Text causes NETWORKDAYS to treat it as zero; always check with ISNUMBER.
2. Omitting Holiday Parameter in Both Functions – Passing holidays to NETWORKDAYS but forgetting to repeat it in WORKDAY leads to mismatched counts and list lengths.
3. Reversed Date Order – If StartDate exceeds EndDate, NETWORKDAYS returns negative numbers, causing SEQUENCE to spill a #VALUE! error. Protect with IF(StartDate>EndDate,\"\",...).
4. Hard-coding Weekend Pattern Everywhere – Entering \"0000001\" in multiple formulas increases maintenance burden. Store once in [Config] and reference by cell.
5. Turning Dynamic Arrays into Volatile Copies – Copy-paste values mid-project, then forget, resulting in out-of-date lists. Label static copies clearly or color them to avoid stale data.

Alternative Methods

While dynamic-array formulas are preferable, other routes exist.

Choose Power Query if you need repeatable but static outputs in SharePoint; choose VBA when distributing a template to mixed Office versions; stick with formulas for responsive, interactive models.

FAQ

When should I use this approach?

Use the dynamic formula whenever your workbook must recalculate instantly—project schedules, capacity planning, or any dashboard that allows the user to move sliders for date ranges.

Can this work across multiple sheets?

Yes. Store StartDate and EndDate on Sheet1, holidays on Config, and place the formula on Schedule. Reference them using qualified sheet names, e.g., =WORKDAY(Config!B2-1,SEQUENCE(NETWORKDAYS(Config!B2,Config!B3,Config!H2:H20)),Config!H2:H20).

What are the limitations?

Dynamic arrays require Microsoft 365 or Excel 2021. Earlier versions will not spill automatically. Additionally, formulas recalculate whenever source cells change; for spreadsheets containing hundreds of thousands of rows, performance can become sluggish.

How do I handle errors?

Wrap the core formula in IFERROR to capture situations like reversed dates or missing inputs:

=IFERROR(WORKDAY(StartDate-1,SEQUENCE(NETWORKDAYS(StartDate,EndDate,Holidays)),Holidays),"Check inputs")

Does this work in older Excel versions?

NETWORKDAYS and WORKDAY exist since Excel 2007, so you can replicate logic with helper columns. However, SEQUENCE, FILTER, LET, and MAP require Microsoft 365 or Excel 2021. For older versions, switch to Power Query or VBA.

What about performance with large datasets?

For lists under 20,000 rows, modern Excel calculates near-instantaneously. Beyond that, consider converting the spilled range to values once planning is finalized or offload the generation to Power Query, which handles row streaming more efficiently.

Conclusion

Being able to list workdays between two dates is a small skill with big ripple effects. It enables accurate timelines, clean payroll calculations, and dependable delivery schedules while reducing manual edits. By mastering dynamic-array formulas like WORKDAY combined with SEQUENCE and NETWORKDAYS, you gain a flexible, transparent solution that can adapt to varying weekends, regional holidays, and future calendar changes. Continue exploring advanced Excel functions such as LET, LAMBDA, and Power Query to extend these capabilities even further, and soon you will handle any scheduling challenge with confidence and speed.