How to Filter Excel Rows That Fall Within the Current Week
Question details
The user needs to return only the rows in an Excel/WPS spreadsheet whose dates fall within the current week.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a date-based list, such as a donor list, and needs to display only rows for the current week.
- Observed behavior
- Using WEEKNUM directly on the full date range inside FILTER does not reliably return the expected current-week rows, so a helper column is required.
Confirm that the column you are filtering contains real date values, not text, and add a blank helper column next to it for the WEEKNUM results. If you plan to combine multiple sheets, use the same helper-column position on every sheet.
Use a WEEKNUM Helper Column and the FILTER Function
Use this reliable method when you want to return all rows whose dates fall in the current week without applying WEEKNUM directly to a range inside FILTER.
WEEKNUM may not evaluate reliably over an entire date range inside FILTER in all spreadsheet versions. A helper column calculates the week number row by row, so FILTER can compare a simple helper range to WEEKNUM(TODAY()).
In your source sheet, add a blank column next to the date column, for example column E in the Donor sheet.
In the first data cell of the helper column, enter =WEEKNUM(A2) and fill it down through every date row.
In a blank results cell, enter =IFERROR(FILTER(Donor!A:D,Donor!E:E=WEEKNUM(TODAY())),"").
Press Enter to spill the matching rows for the current week. IFERROR displays a blank result when no rows match.
Combine Multiple Sheets Before Filtering Current-Week Rows
Use this method when your date rows are split across several worksheets and you want a single current-week result list.
Filter Current-Week Excel Rows in WPS Spreadsheets with a Helper Column
Open your workbook in WPS Spreadsheets, add a WEEKNUM helper column next to your dates, and use the FILTER function to return only the rows whose week number equals the current week. The IFERROR wrapper keeps the result clean when no rows match.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select your Excel workbook containing the date rows.
- 2. Add the WEEKNUM helper column: Insert a blank column next to your date column, then enter =WEEKNUM(A2) in the first data row and fill it down through all rows.
- 3. Enter the FILTER formula: In a blank result cell, enter =IFERROR(FILTER(Donor!A:D,Donor!E:E=WEEKNUM(TODAY())),""). Adjust the sheet name and range to match your workbook.
- 4. Review the current-week rows: Press Enter and verify that only rows whose helper value equals WEEKNUM(TODAY()) are returned. A blank result means there are no current-week rows.
Frequently Asked Questions
Why does WEEKNUM not work directly inside FILTER over a whole date range?
In many spreadsheet versions, WEEKNUM does not return a reliable array when given an entire range inside FILTER. Add a helper column such as =WEEKNUM(A2) and compare that helper range to WEEKNUM(TODAY()).
How do I filter rows from multiple sheets for the current week?
Add the same WEEKNUM helper column to each sheet, then use a formula such as =IFERROR(FILTER(VSTACK(Sheet1!A:E,Sheet2!A:E),VSTACK(Sheet1!E:E,Sheet2!E:E)=WEEKNUM(TODAY())),"") in a results sheet.
What does IFERROR do in the current-week FILTER formula?
IFERROR returns an empty string or custom message instead of an error when FILTER finds no rows matching the current week.
Can I highlight today’s rows instead of the whole week?
Yes. Use =FILTER(Donor!A:D,Donor!A:A=TODAY()) after confirming that column A contains real date values.
Does WPS Spreadsheets save Excel files in Microsoft Excel format?
Yes. WPS Spreadsheets can open and save .xlsx, .xls, and other supported formats while preserving formulas and layout.
