How to Filter Dates from the Last 10 Days in Excel
Question details
The user needs to filter a dataset or PivotTable to display only records with dates falling within the previous 10 days.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing time-sensitive data where only recent records from the last 10 days are relevant for reporting and analysis.
- Observed behavior
- The user wants to set up a dynamic or static filter to isolate dates from the last 10 days, either using PivotTable tools or standard table filters.
Ensure your date column is formatted as valid date values rather than text; otherwise, the date filters and mathematical formulas will not calculate correctly.
Use PivotTable Date Filters
Apply the built-in 'Between' date filter directly within a PivotTable to specify a custom date range for the past 10 days.
If your data is already summarized in a PivotTable, Excel provides built-in date filters that make it easy to isolate specific timeframes without altering the source data.
In your PivotTable, locate the Date field and click the drop-down arrow next to the field header.
Hover over 'Date Filters' in the drop-down menu and select 'Between...' from the secondary menu.
In the dialog box, enter the date from 10 days ago in the start date box and today's date in the end date box, then click 'OK' to apply the filter.
Create a Helper Column with a Dynamic Formula
Use a dynamic formula in a new column to automatically identify dates from the last 10 days, then filter by this column.
Filter Dates Dynamically in WPS Spreadsheet
WPS Spreadsheet provides intuitive tools for PivotTables and dynamic formulas, allowing you to easily filter dates from the last 10 days just like you would in Excel. Enjoy seamless data processing and a familiar interface.
- 1. Open your Workbook: Launch WPS Spreadsheet and open the file containing your date records.
- 2. Add a Helper Column: Create a new column and type the formula =A2>NOW()-10 (assuming your date is in A2), then drag the fill handle to apply it to all rows.
- 3. Enable AutoFilter: Navigate to the 'Data' tab on the top ribbon and click the 'AutoFilter' icon.
- 4. Apply the Filter: Click the filter drop-down on your new helper column, deselect 'FALSE', keep 'TRUE' checked, and click 'OK'.
Frequently Asked Questions
Can I use a built-in standard filter for the last 10 days without a formula?
Standard Excel table filters have built-in dynamic options for 'Last Week', 'Last Month', or 'Today', but they do not have a specific 'Last 10 Days' option. Using a helper column with a formula is the most reliable way to achieve this exact timeframe dynamically.
Why is my NOW()-10 formula returning a #VALUE error?
This error typically occurs if the data in your date column is formatted as text rather than a serial date. To fix this, select your date column, go to 'Data' > 'Text to Columns', click 'Finish', and format the column as Date.
Does the helper column formula update automatically tomorrow?
Yes. The NOW() function is volatile, meaning it automatically recalculates based on your computer's system clock. Every time you open the workbook or calculate the sheet, the 10-day window shifts to remain accurate.
