Formula Errors

How to Count Holiday Dates Within a Date Range Including Today in Spreadsheets

Algirdas Jasaitis Oct 1, 2026 869 views

Question details

The user needs to calculate the number of holiday dates falling between a specific start and end date interval that encompasses the current date (TODAY()).

Product: Spreadsheets
Device & OS: not provided
Scenario: Tracking holidays within a dynamic date interval that updates based on today's date, utilizing nested lookup and counting functions.
Observed behavior: An existing COUNTIFS formula combined with XLOOKUP returns an error or selects the wrong timeframe because the XLOOKUP match mode inadvertently targets the preceding or subsequent interval.

Before you start

Ensure you have a structured reference table for your start and end date intervals, as well as a separate, clearly defined cell range containing your designated holiday dates.

Solution 1Recommended

Correct XLOOKUP Match Mode and Combine with LET and COUNTIFS

Adjust the match mode in XLOOKUP to accurately identify the date range containing today, then use the LET function to pass these dates into a COUNTIFS calculation.

When using XLOOKUP to find a date interval for TODAY(), the match mode is critical. A default match mode or a match mode of 1 may cause the formula to grab the preceding or next timeframe. By setting the match mode to -1 (exact match or next smaller item), you ensure the correct interval is captured.

Define variables with LET

Start your formula by typing =LET( to declare variables for your start and end dates. This prevents you from having to write complex XLOOKUPs multiple times within the same formula.

Configure XLOOKUP for the start date

Create a variable (e.g., 'StartDate') and use XLOOKUP to search for TODAY() within your date intervals list. Crucially, set the match_mode argument to -1 so the formula selects the correct current interval.

Retrieve the matching end date

Create a second variable (e.g., 'EndDate') to retrieve the corresponding end date for the interval you just identified.

Count the holidays with COUNTIFS

For the final calculation argument in the LET function, write a COUNTIFS formula referencing your 'Holidays' range. Set the criteria to count dates that are ">="&StartDate and "<="&EndDate.

Correct XLOOKUP Match Mode and Combine with LET and COUNTIFS

Pro Tip: Changing the XLOOKUP match mode from 1 to -1 is typically the key to resolving offset date range selection errors in chronological lookup tables.

Solution 2

Use NETWORKDAYS.INTL for Working Days Instead

If the ultimate goal is to find net working days while excluding holidays in the current interval, NETWORKDAYS.INTL provides a more straightforward approach.

Advanced Formula Support

Easily Manage Complex Date Formulas with WPS Spreadsheet

WPS Office provides a powerful spreadsheet application that seamlessly handles advanced dynamic array functions like LET, XLOOKUP, and COUNTIFS, helping you manage complex date intervals without errors.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your date intervals and holiday lists.
2. Input the LET formula: Click on your target cell and type =LET( to begin structuring your dynamic variables using the built-in formula guide.
3. Select ranges visually: Use your mouse to highlight your holiday arrays and lookup ranges directly on the sheet to prevent manual typing errors.

Full compatibility with Microsoft Excel formulas, ensuring LET and XLOOKUP work perfectly.Intuitive formula syntax tooltips to help you troubleshoot nested function arguments like match modes.Free and lightweight application for complex data analysis and date calculations.

microsoft office alternative - wps office

Frequently Asked Questions

Why does XLOOKUP return the wrong date interval for TODAY()?

This usually happens because the match mode argument is set incorrectly. If set to 1, it looks for the next larger item. Changing it to -1 (exact match or next smaller item) usually fixes interval selection when dealing with chronological date thresholds.

What is the primary benefit of using the LET function in this formula?

The LET function allows you to assign names to calculation results, such as the dynamic start and end dates. This prevents you from having to calculate the same XLOOKUP formula multiple times inside your COUNTIFS function, improving formula readability and calculation speed.

How do I ensure weekends aren't counted twice if they fall on a holiday?

Instead of using a standard COUNTIFS to tally holidays, consider using the NETWORKDAYS or NETWORKDAYS.INTL functions. These functions automatically exclude weekends by default and will only deduct your specified holidays once, even if they land on a weekend.

Excel & Data Problems