How to List Holidays Between Two Dates in Excel

Why This Task Matters in Excel

Business calendars rarely match the simple Monday-to-Friday workweek. Project managers have to plan timelines that skip statutory holidays, payroll specialists need to prorate salaries when offices close, and customer-service teams forecast staffing around regional holiday schedules. Listing which holidays fall between two arbitrary dates is therefore a critical building block for more advanced calculations such as networked working-day counts, capacity planning, or vacation accruals.

Imagine a North-American manufacturer that promises delivery within ten business days. An order shipped on December 20 might appear on time at first glance, yet Christmas Day and New Year’s Day will quickly derail that calculation. Accurately listing the holidays in the shipping window is the only way to avoid missed service-level agreements, penalty fees, and unhappy customers.

Another scenario arises in financial accounting. Quarter-end closing timelines depend on how many CPA-qualified employees will actually be in the office during the window. A controller who overlooks Good Friday could easily overcommit the team and slip compliance deadlines. Human-resources teams, too, need to reconcile leave balances against regional and floating holidays. Errors here expose organizations to overtime premiums or underpayment liabilities.

Excel remains the de-facto planning tool across industries because of its flexibility, transparent logic, and near-universal availability. Whether you are an analyst in a global firm with Microsoft 365 or a small non-profit on an older desktop version, Excel’s mixture of dynamic array functions, logical operators, and lookup capabilities can produce an instant, verifiable list of holidays. Neglecting to master this skill can produce cascading errors in project schedules, staffing forecasts, and even cash-flow projections. Conversely, learning to dynamically list holidays connects directly to other core Excel workflows such as calculating business days with NETWORKDAYS, validating date inputs with data-validation drop-downs, and driving dashboards that highlight upcoming office closures.

Best Excel Approach

The most modern and robust method is to combine the FILTER function with logical tests inside a dynamic array formula. FILTER instantly returns only the items in a list that satisfy a given condition, and in Microsoft 365 it “spills” the result into as many cells as needed—no copying, no helper columns, no VBA. For anyone on older perpetual licenses, an INDEX + SMALL array approach or an Advanced Filter can replicate the logic, but FILTER remains the gold standard for simplicity, transparency, and speed.

Prerequisites:

• Microsoft 365 or Excel 2021 (for FILTER)
• A dedicated range that stores every holiday date you want to consider—this can be a named range called Holidays or a structured column in a Table.
• Two input cells, StartDate and EndDate, containing valid Excel date serials.

Logical flow:

1. Test each holiday to see whether it is on or after StartDate.
2. Test each holiday to see whether it is on or before EndDate.
3. Multiply the two Boolean arrays so that only holidays satisfying both conditions return TRUE.
4. Pass that Boolean array into FILTER to keep only the TRUE rows.

Syntax:

=FILTER(Holidays, (Holidays>=StartDate)*(Holidays<=EndDate), "No holidays")

Alternative (older versions):

=IFERROR(INDEX(Holidays, SMALL(IF((Holidays>=StartDate)*(Holidays<=EndDate), ROW(Holidays)-MIN(ROW(Holidays))+1), ROWS($A$1:A1))), "")

Entered with Ctrl+Shift+Enter in Excel 2019 or earlier, then filled down as far as necessary.

Parameters and Inputs

• Holidays – Required. A vertical range or a structured column containing purely date values. A Table is strongly recommended because it expands automatically.
• StartDate – Required. A single-cell date marking the beginning of your window.
• EndDate – Required. A single-cell date marking the end of your window.
• IncludeTextOutput (optional in variations) – When using TEXTJOIN or custom labels, you can choose between returning dates or formatted strings.
• SpillDestination – The first cell where you type the FILTER formula. Excel will automatically manage the spill range, but you must ensure no data blocks its path.

Data preparation:

• Make certain all holiday cells are actual dates, not text that “looks like” a date. Use the DATEVALUE function or Text-to-Columns if necessary.
• Sort the holiday list for readability, although FILTER will work even if unsorted.
• Validate that StartDate is less than or equal to EndDate. If user input is uncertain, add a check: `=IF(`StartDate>EndDate,\"Start must precede End\",\"\")
• Edge cases: If the window includes no holidays, FILTER’s optional third argument (“No holidays”) prevents a #CALC! error. For the legacy formula, IFERROR(\"\", \"\") provides similar protection.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose a small office closes on five fixed dates in 2024: New Year’s Day (Jan 1), Memorial Day (May 27), Independence Day (Jul 4), Labor Day (Sep 2), and Christmas Day (Dec 25). You keep those in [B2:B6].

Step 1 – Set up input cells: place StartDate in [E2] and EndDate in [E3]. Enter 5-May-2024 and 15-July-2024 respectively, or use the date picker available in recent Excel builds.

Step 2 – Name the holiday range. Select [B2:B6] and type Holidays in the Name Box. Press Enter.

Step 3 – In [G2] type the dynamic formula:

=FILTER(Holidays, (Holidays>=E2)*(Holidays<=E3), "No holidays")

Press Enter. Excel instantly spills two cells: 27-May-2024 and 4-Jul-2024.

Why it works: The comparison Holidays>=E2 creates a Boolean array such as [FALSE, TRUE, TRUE, FALSE, FALSE]. Likewise Holidays<=E3 yields [TRUE, TRUE, TRUE, TRUE, TRUE]. Multiplying returns [0,1,1,0,0]. FILTER keeps rows where the product equals 1.

Variations: Change EndDate to 20-May-2024 and the result now reads “No holidays,” demonstrating robust handling of empty returns.

Troubleshooting: If you see a #SPILL! error, check for cells with content underneath the formula’s spill area. Clear or move the obstructing data.

Example 2: Real-World Application

A regional logistics firm operates internationally and tracks holidays from multiple countries. You maintain a Table named tblHolidays with columns Country, HolidayDate, HolidayName. The operations manager needs to know which German holidays fall between specific pickup and delivery dates for customs paperwork.

Step 1 – Sage data. In Sheet HolidayData, tblHolidays lists 120 rows covering years 2023-2026.

Step 2 – In Sheet Planner, cell [B2] (Country) lets users select from a data-validation drop-down (Germany, France, USA, etc.). Cells [B3] and [B4] hold the StartDate and EndDate chosen via calendar control.

Step 3 – Build a helper column in the Table (optional) called IsWithinWindow with formula (structured reference):

=[@[HolidayDate]]>=Planner!B3  *  [@[HolidayDate]]<=Planner!B4

However, helper columns bulk up the workbook. A more elegant dynamic array version in [D6] of Planner eliminates that helper:

=FILTER(tblHolidays[HolidayName],
   (tblHolidays[Country]=B2)*
   (tblHolidays[HolidayDate]>=B3)*
   (tblHolidays[HolidayDate]<=B4),
   "None")

To display both the name and date sorted chronologically, wrap LET and SORT:

=LET(
   list, FILTER(tblHolidays[[HolidayDate]:[HolidayName]],
       (tblHolidays[Country]=B2)*
       (tblHolidays[HolidayDate]>=B3)*
       (tblHolidays[HolidayDate]<=B4)),
   SORT(list, 1, 1)
)

Result: A two-column spill showing each relevant holiday date alongside its description, perfectly ordered.

Integration: The customs paperwork template references this spill range to auto-populate documentation headers. Because FILTER is volatile to changes in B2, B3, or B4, the list instantly refreshes when planners tweak shipping windows.

Performance: Even with 10 000 holiday rows across many years and regions, modern Excel recalculates the filter in milliseconds because it works on memory-resident arrays. Storing the list in a Table prevents formula breakage when you append next year’s holidays.

Example 3: Advanced Technique

Finance departments frequently need a rolling view: “Show me the next ten upcoming holidays after today.” This requirement combines dynamic date inputs, row-limiting, and informative formatting.

Step 1 – Put TODAY() in a named formula CurrDate or directly reference `=TODAY(`) in cell [I2].

Step 2 – Assume Holidays2020-2030 lives in named range Holidays.

Step 3 – Combine FILTER with SORT and TAKE (Microsoft 365 only):

=LET(
   upcoming, FILTER(Holidays, Holidays>=I2),
   sorted, SORT(upcoming, 1, 1),
   TAKE(sorted, 10)
)

Explanation:

• FILTER removes all past holidays.
• SORT arranges them chronologically.
• TAKE restricts the list to the first ten rows.

Edge cases:

• If fewer than ten future holidays exist (for example, at year end), TAKE will simply return all available rows without error.
• If the holiday list contains duplicates (common when a Table lists both Federal and Company holidays with identical dates), wrap UNIQUE before SORT.

Formatting: Apply a custom number format “ddd, mmm dd, yyyy” to display “Mon, Jan 01, 2024”. Use Conditional Formatting to highlight holidays falling on Monday or Friday to flag long weekends.

Professional tip: Wrap the formula inside NAME MANAGER as UpcomingHolidays. Business dashboards can now reference =UpcomingHolidays as if it were a range that automatically resizes.

Tips and Best Practices

1. Store your holidays in an Excel Table. Tables auto-extend and keep formulas referencing them intact.
2. Use named ranges (StartDate, EndDate) or named formulas (CurrDate) to clarify logic, reduce errors, and simplify maintenance.
3. Prefer dynamic arrays like FILTER, SORT, and TAKE for transparency and speed; avoid volatile INDIRECT unless absolutely necessary.
4. Combine holiday lists from multiple jurisdictions in one master Table with a Region column, then filter by region rather than juggling multiple ranges.
5. Format your output dates consistently with the rest of your workbook to avoid confusion; custom formats like “mmmm dd” work well for presentation, whereas ISO 8601 “yyyy-mm-dd” is optimal for data exchange.
6. Document assumptions (e.g., which holidays are statutory vs. company discretionary) in a note next to the holiday Table so future maintainers know when to update it.

Common Mistakes to Avoid

1. Mixing text and real dates – If a single “date” is actually text, comparisons like >= will silently fail. Fix by wrapping DATEVALUE or using error-checking rules.
2. Reversing start and end dates – A common user oversight. Add a data-validation rule that StartDate ≤ EndDate or show a warning message.
3. Forgetting dynamic array expansion – Placing the FILTER formula in a column with existing data below causes #SPILL!. Always reserve a blank column or place formulas in their own sheet.
4. Copy-pasting holidays without clearing formats – Hidden time components produce unexpected mismatches. Use VALUE to strip times or set cell format to Short Date before pasting.
5. Using volatile TODAY() in massive workbooks – While convenient, TODAY recalculates every time the sheet changes. For very large models, replace with a static date input updated manually during each analysis session.

Alternative Methods

While FILTER is ideal in modern Excel, other strategies can produce similar results.

When to choose:

• Use FILTER whenever possible for live dashboards.
• Use Power Query for massive, multi-source holiday tables that refresh from databases.
• Use Advanced Filter or INDEX+SMALL if constrained to legacy Excel at a client site.
• Opt for VBA only when distributing a template to users who must remain on Excel 2010 without array familiarity.

FAQ

When should I use this approach?

Deploy the dynamic FILTER solution anytime you need an always-up-to-date list that recalculates the instant users change either date boundary. It is perfect for interactive project schedules, staffing planners, or dashboards where automation trumps manual control.

Can this work across multiple sheets?

Yes. Store the holiday Table on a hidden sheet (HolidayData) and reference it in formulas on Planner or Dashboard sheets by using structured references like HolidayData!tblHolidays[HolidayDate]. Dynamic arrays will still spill on the destination sheet with no extra work.

What are the limitations?

The FILTER method requires Microsoft 365 or Excel 2021. In addition, dynamic arrays cannot spill across non-adjacent ranges, and they cannot overwrite existing data. In legacy Excel you must fall back to array formulas or other workarounds.

How do I handle errors?

Provide FILTER’s third argument to display a friendly message when no holidays fall in the window. For older INDEX+SMALL formulas, wrap IFERROR around the entire construct. Validate user input with data-validation rules to prevent StartDate later than EndDate.

Does this work in older Excel versions?

Yes, with modifications. Use the INDEX+SMALL array formula or leverage Advanced Filter. Remember to commit array formulas with Ctrl+Shift+Enter and pre-fill enough rows to accommodate the largest possible holiday list.

What about performance with large datasets?

Dynamic arrays in Microsoft 365 are highly optimized. Even filtering 50 000 holiday rows recalculates almost instantly. For hundreds of thousands of rows, Power Query or a backend database is advisable; then load only the filtered subset into Excel.

Conclusion

Mastering the skill of listing holidays between two dates elevates your scheduling, forecasting, and compliance work from guesswork to precision. The modern FILTER function delivers a clean, update-proof solution, while alternative methods keep you covered in older environments. Once you can dynamically surface holidays, you can feed that result into business-day counts, Gantt charts, staffing models, and more. Continue exploring Excel’s dynamic array ecosystem, experiment with LET and LAMBDA for even cleaner logic, and soon you will transform complex calendar calculations into a single, crystal-clear formula. Happy planning!