Function Problems

How to Filter Employee Names by a Rolling 12-Month Date Range in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to extract a list of employee names whose start dates fall within the past 12 months, ensuring the list dynamically updates over time without showing the date values.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an auto-updating report or list of recent hires, or tracking employees active within a rolling one-year window.
Observed behavior: Requires a formula or query mechanism that automatically adjusts the 12-month window relative to the current date and isolates only the 'Name' column from the data table.

Before you start

Ensure your Excel version supports Dynamic Array functions like FILTER (available in Microsoft 365 and Excel 2021 or later). Verify that your employee data is organized as a structured Excel Table for easier formula referencing.

Solution 1Recommended

Use the FILTER and EDATE Functions

The most efficient way to dynamically filter records based on a rolling date in modern versions of Excel.

By combining the FILTER function with EDATE and TODAY, Excel can continuously calculate the past 12 months without manual updates. Selecting only the 'Name' column in the first argument ensures the dates are not copied into the results.

Select the target cell

Click on the empty cell where you want the dynamic list of names to begin displaying.

Enter the FILTER formula

Type the formula: =FILTER(Employees[Name], Employees[Start Date] >= EDATE(TODAY(), -12)). Adjust 'Employees[Name]' and 'Employees[Start Date]' to match the actual names of your table columns.

Apply the formula

Press Enter. The list will automatically populate with names and update each day based on the current date.

Formula Breakdown: TODAY() returns the current date, EDATE(..., -12) subtracts exactly 12 months, and FILTER extracts only the names that meet this condition.

Solution 2

Filter Using Power Query

Ideal for older versions of Excel or when handling massive datasets from external sources that require automated cleaning.

Advanced Spreadsheet Solution

Dynamically Filter Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like FILTER and EDATE, allowing you to manipulate and extract dynamic date-based records easily without relying on complicated macros.

1. Open your data in WPS: Launch WPS Spreadsheet and open the workbook containing your employee records.
2. Select an output cell: Click on a blank cell where you want the filtered list of names to appear.
3. Input the array formula: Type =FILTER(A2:A100, B2:B100>=EDATE(TODAY(),-12)), replacing A and B with your actual Name and Date column ranges.
4. Generate the list: Press Enter to instantly generate the auto-updating list of employees hired within the last 12 months.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Supports dynamic arrays and modern functions for complex data filtering.Lightweight architecture ensures smooth performance even with large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER function returning a #CALC! error?

The #CALC! error occurs when the FILTER function finds no records that match the given criteria (e.g., no employees were hired in the last 12 months). You can add a third argument to handle this gracefully, such as: =FILTER(Names, Dates>=EDATE(TODAY(),-12), "No recent hires").

Does the EDATE function properly account for leap years?

Yes, the EDATE function accurately calculates the same day of the month in the past or future, automatically adjusting for the end of the month during leap years when necessary.

How can I sort the dynamically filtered names alphabetically?

You can wrap your FILTER formula inside a SORT function. For example, use: =SORT(FILTER(Employees[Name], Employees[Start Date]>=EDATE(TODAY(),-12))) to automatically alphabetize the extracted names.

Will the FILTER function work in older versions of Excel?

The FILTER function is only available in Microsoft 365, Excel 2021, and newer versions. If you are using Excel 2019 or earlier, you should use the Power Query method to achieve the same result.

Excel & Data Problems