How to Filter Excel Rows from Today Through the Next Six Weeks
Question details
The user wants to create a dynamic formula to filter and return entire rows of data where the date falls within a rolling 42-day window starting from today.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Tracking schedules, upcoming deadlines, or events over a six-week rolling window without needing to filter data manually every day.
- Observed behavior
- Needs a dynamic solution that automatically updates daily based on the current system date.
Ensure your dataset is organized with clear headers and that the column you are checking contains valid date values rather than text strings.
Use the Dynamic FILTER and TODAY Functions
Combine the FILTER function with the TODAY function to automatically extract rows for the upcoming six weeks.
This method uses logical multiplication (*) to apply an AND condition within the FILTER function. It checks if a date is greater than or equal to today, AND less than or equal to 42 days from today. Because it uses the TODAY() function, the results will shift forward automatically each day.
Click on an empty cell where you want the top-left corner of your filtered data to appear. Ensure there is plenty of blank space below and to the right.
Type the formula: =FILTER(A2:G1000, (A2:A1000>=TODAY())*(A2:A1000<=TODAY()+42))
Modify 'A2:G1000' to encompass your entire dataset. Modify 'A2:A1000' to target the specific column that contains your dates.
Press Enter. The formula will automatically spill the matching rows into the adjacent cells and update daily.
Apply Custom Date Filters via the Data Tab
If you are using an older version of spreadsheet software that doesn't support the dynamic FILTER function, use the built-in Custom AutoFilter.
Use WPS Spreadsheet to Handle Dynamic Arrays
WPS Office fully supports advanced dynamic array formulas like FILTER, allowing you to build automated, self-updating dashboards with zero hassle.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing dataset.
- 2. Input the dynamic formula: Click an empty cell and enter your =FILTER(..., ...) formula with the TODAY() conditions.
- 3. Save with seamless compatibility: Press Enter to instantly spill the results, then save your document in .xlsx format to share with Microsoft Excel users seamlessly.
Frequently Asked Questions
Why is my FILTER formula returning a #CALC! error?
A #CALC! error usually occurs when the FILTER function finds no records matching your criteria. You can add an optional third argument to handle this gracefully, like this: =FILTER(A2:G1000, criteria, "No upcoming dates").
Does the TODAY() function update automatically?
Yes, the TODAY() function is volatile. It will automatically recalculate and update the dates every time you open the workbook or whenever any calculation is triggered in the spreadsheet.
How can I change the range to exactly one month instead of six weeks?
You can change the '+42' to '+30' for an approximation, or use the EDATE function for an exact calendar month by updating the second condition to: <=EDATE(TODAY(),1).
Will this FILTER formula work on older versions of Excel?
The FILTER function is a dynamic array function available in Microsoft 365, Excel 2021, and modern versions of WPS Office. Older versions (like Excel 2016 or 2019) do not support it, and you will need to use standard Data Filters or complex INDEX/MATCH arrays instead.
