How to Find Expired Records Using FILTER and BYROW in Excel
Question details
Extract names from an Excel table where all relevant date fields in the person's record are older than a specific number of days, such as 275 days.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Evaluating multiple date columns for each person dynamically to determine if their records have expired, and returning a list of their names.
- Observed behavior
- Needs a combined dynamic array formula using FILTER, BYROW, and LAMBDA to evaluate the rows across multiple columns and return the matching names.
Ensure that your data is formatted as an official Table (e.g., named 'Admin') and that all date columns contain valid, serial date values rather than plain text strings.
Use FILTER, BYROW, and LAMBDA to Extract Expired Names
This method evaluates each row dynamically to find the minimum date across multiple columns and filters the names based on the expiration condition.
The BYROW function passes each row of the specified date columns to a custom LAMBDA function. The LAMBDA function then calculates the minimum (earliest) date for that row. Finally, the FILTER function checks if this minimum date is older than today minus 275 days and returns the matching names.
Click on the empty cell where you want the dynamic list of expired names to populate.
Type the following formula into the formula bar: =FILTER(Admin[Name], BYROW(Admin[[Privileging Letter Ex Date]:[HSD 500]], LAMBDA(r, MIN(r)))<TODAY()-275, "")
Modify 'Admin' to match your table's name, and change the range '[Privileging Letter Ex Date]:[HSD 500]' to accurately reflect the start and end of your date columns.
Press Enter to generate the dynamic array of names. The list will automatically update as dates change or new rows are added to the table.
Handle Complex Array Formulas Easily with WPS Office
WPS Spreadsheet fully supports modern dynamic array functions like FILTER, BYROW, and LAMBDA, allowing you to build complex data analysis models and filter records effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data table.
- 2. Select the target cell: Click an empty cell where the filtered list should appear.
- 3. Input the array formula: Type your combined =FILTER() and =BYROW() formula exactly as you would in Excel.
- 4. Generate results: Press Enter. WPS Spreadsheet will automatically spill the results into the adjacent cells.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no matching records (i.e., no dates are older than 275 days). You can prevent this by providing a fallback value in the third argument of the FILTER function, such as "" (to leave it blank) or "No expired records".
Can I test if any date has expired instead of the earliest date?
Yes. If your logic requires checking if any date is older than 275 days, or you need to evaluate the most recent date instead of the earliest, you can change MIN(r) to MAX(r) inside your LAMBDA function, depending on your exact expiration rules.
Do BYROW and LAMBDA work in older versions of Excel?
No, BYROW and LAMBDA are modern dynamic array functions available only in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you are using older software, you will need to rely on helper columns or complex INDEX and AGGREGATE formulas.
