Function Problems

How to Find Holidays Within 7 Days Before a Date in Excel

Steven ParkerSteven Parker Nov 3, 2026 869 views

Question details

The user needs a method to check whether a holiday took place during the seven days leading up to a specific activity date in Excel.

How to Find Holidays Within 7 Days Before a Date in Excel
Product
Excel
Device & OS
not provided
Scenario
Comparing a column of activity dates against a designated lookup range of holiday dates to find matches within a rolling 7-day retrospective window.
Observed behavior
The lookup formula should return the corresponding holiday name if one occurred within the prior 7 days, or leave the cell blank if no holiday is found.
Before you start

Ensure you have created a reference table in your workbook containing a list of holiday dates and their corresponding names. For formula stability, consider naming this reference range or converting it into an official Excel Table.

Solution 1Recommended

Use an Array LOOKUP Formula to Find Recent Holidays

Apply a conditional lookup formula that checks both the 7-day date parameter and the holiday reference list to return the matching holiday name.

To identify if a holiday falls within a 7-day window prior to your target date, you can use a combination of LOOKUP and array-based condition checks. This systematically compares your target activity date minus 7 days against your defined holiday dataset.

1
Set up the holiday reference table

In an empty area of your worksheet or on a separate sheet, enter your holiday dates in one column (for example, H2:H10) and the corresponding holiday names in the adjacent column (I2:I10).

2
Input the array lookup formula

Select the empty cell next to your first activity date (e.g., cell B2). Enter a formula that calculates the 7-day period, such as `=IFERROR(LOOKUP(2,1/((H$2:H$10>=A2-7)*(H$2:H$10<=A2)),I$2:I$10), "")`, where A2 is the activity date.

3
Apply the formula to the column

Press Enter to execute the formula. Then, click the small square (fill handle) at the bottom-right corner of the cell and drag it down to populate the formula for all remaining activity dates.

Use an Array LOOKUP Formula to Find Recent Holidays
Using Absolute References: Including dollar signs ($) in your holiday lookup range (like H$2:H$10) ensures that the reference list does not shift or break when you drag and copy the formula down your dataset.
Advanced Formulas in WPS Spreadsheet

Manage Dates and Lookups Effortlessly with WPS Office

WPS Spreadsheet provides robust support for all advanced array and lookup functions, making complex date calculations simple and highly efficient.

  1. 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your activity dates and holiday list.
  2. 2. Use the Function Wizard: Navigate to the 'Formulas' tab on the ribbon and click 'Insert Function' to easily search for and build your LOOKUP or MATCH formula with guided steps.
  3. 3. Format and Fill: Confirm the formula in your target cell, use the fill handle to drag it down the data column, and utilize the 'Format Cells' menu to adjust date formats as needed.
100% compatible with Microsoft Excel formulas, XLOOKUP, and date functionsLightweight and extremely fast for processing large datasetsIncludes built-in templates for project and schedule managementCross-platform support across Windows, Mac, Linux, iOS, and Android
microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP instead of older formulas for finding date ranges?

Yes. If you are using a newer version of Excel or WPS Office, you can utilize XLOOKUP with a specific search mode and mathematical conditions to locate dates within a range. This approach is often easier to read and maintain.

Why is my date formula returning a 5-digit number instead of a date?

Spreadsheet software stores dates as sequential serial numbers (e.g., 44500). If your formula returns a number instead of a formatted date, right-click the cell, select 'Format Cells', and change the category to 'Short Date' or 'Long Date'.

How do I calculate working days excluding both weekends and holidays?

You can use the built-in NETWORKDAYS or NETWORKDAYS.INTL functions. These functions automatically exclude weekends from your duration and provide an optional argument to specify a custom range of holiday dates to subtract.