How to Find Holidays Within 7 Days Before a Date in Excel
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.

- 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.
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.
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.
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).
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.
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.

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. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your activity dates and holiday list.
- 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. 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.

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.




