Extract Excel Records into a New Table by Date
Question details
Extract records from an Excel table into a separate table or worksheet when the date belongs to a specified month.
- Product
- Microsoft Excel worksheet table
- Device & OS
- not provided
- Scenario
- The user wants to display related values, such as bike names, from form-submitted records when the Date column falls within March or another selected month.
- Observed behavior
- The desired result is a filtered list containing only records whose dates match the target month.
Confirm that the source data includes a valid Date column and is formatted as an Excel table with headers. Check the exact table and column names before entering the formula on the destination sheet.
Use FILTER and MONTH to return records for a specific month
Use this method in Microsoft 365 when you want a dynamic list that automatically updates as new matching records are added to the source table.
Enter the formula in the first cell of the destination list. The results spill into the cells below, so keep the surrounding output area empty.
Select the sheet where you want the filtered results to appear, then click the first output cell, such as A2.
Type =FILTER(Table1[Bike Name],MONTH(Table1[Date])=3,"No matching records") and press Enter. Replace Table1, Bike Name, and Date with the names used in your workbook.
The formula returns each bike name whose corresponding Date value is in March. If no rows match, it displays No matching records.
Replace =3 with another month number, such as =4 for April or =12 for December. You can also reference a cell containing the month number, for example MONTH(Table1[Date])=B1.
Create a Dynamic Monthly Record List in WPS Spreadsheets
WPS Spreadsheets lets you filter date-based records into a new worksheet with dynamic array formulas. Use FILTER with MONTH to return bike names or entire rows for the selected month, then update the month criterion whenever you need another report.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the .xlsx or .xls workbook containing the source table.
- 2. Select the output location: Add a new worksheet with the plus button, or select an existing report sheet, then click the first cell for the results.
- 3. Enter the monthly FILTER formula: Enter =FILTER(Table1[Bike Name],MONTH(Table1[Date])=3,"No matching records") and replace the table and column references with your own names.
- 4. Use a cell as the month selector: Enter a month number, such as 3, in B1 and change the condition to MONTH(Table1[Date])=B1 to make the report reusable.
Frequently Asked Questions
How do I extract all columns for records dated in March?
Use the complete table as the return array: =FILTER(Table1,MONTH(Table1[Date])=3,"No matching records"). Enter it in the top-left cell of an empty output area.
Can I filter records by both month and year?
Yes. Add both conditions to the include argument, for example: =FILTER(Table1,(MONTH(Table1[Date])=3)*(YEAR(Table1[Date])=2024),"No matching records").
Why does the FILTER formula return no matching records?
Verify that the Date column contains real Excel date values rather than text, and confirm that the target month number is correct. You can test a date cell by changing its format to Short Date from the Home tab.
How can I choose the month without editing the formula each time?
Enter the month number in a cell, such as B1, then use that cell in the formula: =FILTER(Table1[Bike Name],MONTH(Table1[Date])=B1,"No matching records").
