How to Filter WPS Spreadsheets Pivot Tables by Dynamic Date Ranges
Question details
The user needs two WPS Spreadsheets PivotTables to update automatically based on today's date: one for records assessed more than three months ago and another for records assessed more than two years ago.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Maintaining two rolling reports that must always show records based on the current date without manually changing date filters.
- Observed behavior
- The PivotTables currently require manual date filter updates and do not automatically show records older than three months or older than two years based on today's date.
Confirm the source table includes a recognized assessment date column and that both PivotTables use the same updated source range. The helper-column method requires entering the formula in the source data, not inside the PivotTable.
Add a Calculated Age-in-Months Column and Use It as a PivotTable Filter
Use this method when the PivotTables must automatically show records older than a rolling number of months based on today's date.
This approach moves the rolling date logic into the source data by calculating each record's age in months. Because the formula uses TODAY(), the age recalculates whenever the workbook recalculates or the PivotTable refreshes, and each PivotTable only needs a static numeric filter threshold.
In WPS Spreadsheets, open the workbook containing the source data. Click the column header to the right of the assessment date column, then click the Home tab and choose Insert > Insert Sheet Columns.
In the first new data cell, type =DATEDIF(A2,TODAY(),"m") and press Enter, replacing A2 with the actual assessment date cell reference.
Hover over the fill handle in the lower-right corner of the formula cell and drag down or double-click to fill the formula for every record in the source data.
Select the first PivotTable, click the PivotTable Analyze tab, click Refresh, and drag the new age field to the Filters area in the PivotTable Fields pane.
Open the filter dropdown in the PivotTable, choose Label Filters > Greater Than Or Equal To, enter 3 for the three-month report, and repeat the same filter with 24 for the two-year report.
Create Automatic Rolling Date Filters in WPS Spreadsheets Pivot Tables
Add a DATEDIF helper column in WPS Spreadsheets to age each record by months, then refresh each PivotTable and apply a numeric filter threshold so reports automatically track 3-month and 24-month windows.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel-compatible .xlsx workbook that contains the assessment dates and PivotTables.
- 2. Add the age helper column: Insert a new column in the source data and enter =DATEDIF(date_cell,TODAY(),"m") to calculate months since assessment.
- 3. Refresh and filter each PivotTable: Refresh each PivotTable, add the helper column to the Filters area, then set Greater Than Or Equal To 3 for the three-month report and 24 for the two-year report.
Frequently Asked Questions
Does the DATEDIF helper column update automatically when the assessment date becomes older?
Yes. The formula uses TODAY(), so it recalculates on workbook open or when you refresh the PivotTable. No manual threshold entry is required.
Why does a normal PivotTable date filter not handle this automatically?
Built-in date filters offer fixed ranges and relative periods, but they do not provide a persistent "older than N months from today" threshold across two PivotTables. A calculated month-age field gives you a clean numeric filter.
What if my assessment dates are stored as text?
Convert them to real dates first. In WPS Spreadsheets, select the column and apply a Date format or use DATEVALUE. Otherwise DATEDIF may return a #VALUE! error.
Can both PivotTables use the same helper column?
Yes. Create one age-in-months column in the source data, then filter it as greater than or equal to 3 in the first PivotTable and greater than or equal to 24 in the second.
When should I refresh the PivotTables?
Refresh after opening the workbook or after changing source data. The age values update from TODAY(), and the filters then return the correct rolling records.
