Excel Pivot Table Date Filter: Last 30 Days

Charts & pivot tables · Updated November 19, 2026

To filter an Excel PivotTable to the last 30 calendar days, add a helper column to the source data with a formula that flags dates from today minus 29 days through today, then refresh the PivotTable and filter that helper field to TRUE. A normal PivotTable date filter can set a fixed date range, but a helper field is a dependable way to keep a rolling 30-day window as the calendar advances.

What “last 30 days” means

Before setting the filter, decide what period you mean. A common interpretation is 30 calendar dates including today: today and the previous 29 dates. If today is November 3, for example, that window starts October 5 and ends November 3. This is different from the previous 30 complete days, which would end yesterday, and from the last 30 periods of 24 hours, which depend on the current time.

The distinction matters for dates with timestamps. A value such as November 3 at 2:00 p.m. is stored as a date plus a time in Excel. A filter that includes only values up to midnight at the start of November 3 could omit records from later that day. The helper formula below uses an exclusive boundary at tomorrow, so it includes all times on today’s date.

Set up a rolling 30-day helper field

In the source data, add a column beside the date or date-time column and give it a clear heading, such as Last 30 Days. With source dates in A2:A1000, enter this formula in B2 and fill it down through the records:

=AND(A2>=TODAY()-29,A2<TODAY()+1)

For the example layout, A2:A1000 contains the source dates or date-times and B2:B1000 is the new helper column. If A2 contains October 5 and today is November 3, the formula returns TRUE; if A2 contains October 4 or November 4, it returns FALSE. The start boundary is today minus 29, giving 30 calendar dates when today is included; the end boundary is tomorrow, not inclusive, so times during today are retained.

If the data is formatted as an Excel table, adding the heading and formula beside the table may let Excel expand the table to include the new column. Check that the table range includes the helper field. A table’s calculated-column behavior can also fill the formula down as new rows are added. With a plain cell range, ensure the formula is copied to every source row and that new records are included in the PivotTable’s source range.

Filter the PivotTable to TRUE

  1. Click inside the PivotTable and confirm the source data includes the helper column. If you added the column after creating the PivotTable and it is not listed in the field list, use PivotTable Analyze > Change Data Source to include it.
  2. Choose PivotTable Analyze > Refresh so the PivotTable reads the current source values.
  3. In the PivotTable Fields pane, drag Last 30 Days into the Filters area. You can also place it in Rows or Columns if you want the TRUE/FALSE values visible in the report.
  4. Open the field’s filter dropdown and select TRUE. The PivotTable will show records whose helper formula currently returns TRUE.

On Windows, PivotTable Analyze is a contextual ribbon tab that appears when the PivotTable is selected. Ribbon labels and layout can vary by release. Excel for Mac also has PivotTable tools and supports refreshing and filtering PivotTables, though the contextual ribbon arrangement may differ. In Excel for the web, available PivotTable editing controls can depend on the workbook and feature; if a control is missing, use the desktop app.

Use a fixed date range instead

If you only need a one-time filter, or want to inspect a historical period that should not move tomorrow, use the date field’s own filter. Open the dropdown for the date field in the PivotTable and look under Date Filters for a range option such as Between. Enter the first and last dates you want, then apply the filter. The exact menu choices depend on how the date field is placed and on the Excel version.

A fixed range does not recalculate itself as days pass. If you set October 5 through November 3, those are still the endpoints after the calendar advances. You would need to edit the filter to move the window. Also, a filter using After with a single cutoff may exclude records on the cutoff date, depending on the comparison; use a range and check the displayed results when inclusion of the boundary date matters.

Some date dropdowns offer convenient relative choices such as Today or This Month. Those are not the same as a rolling “previous 30 days” rule. A calendar month can have 28, 29, 30, or 31 days, and a month-to-date filter begins on the first day of the month rather than 29 days before today. The helper field makes the intended rolling interval explicit.

Refresh behavior and keeping the window current

TODAY() returns the current date based on the computer’s date when Excel recalculates. It is a volatile function, so it can update during recalculation, but the PivotTable is a separate report based on source values. Refresh the PivotTable after the helper results change; changing the system date or reopening a workbook does not guarantee the report has refreshed at the moment you expect.

To update the report manually, click in the PivotTable and choose PivotTable Analyze > Refresh. For a workbook with several PivotTables or data connections, Data > Refresh All can refresh multiple items, though it may take longer. If the PivotTable uses the Data Model or an external connection, refresh behavior can also depend on that connection and its settings.

If you want an automatic refresh when the workbook opens, Excel has PivotTable options for refreshing data on file open in desktop versions. The setting is associated with the PivotTable’s data options; availability and exact dialog wording can vary by source and version. Automatic refresh should not be confused with continuous updating: source data must be available, the formulas must recalculate, and the PivotTable must refresh.

Check that the source dates are real dates

A date can look correct while being stored as text. Text dates may sort alphabetically, fail date filters, or produce unexpected helper results. Select a few source cells and inspect the formula bar and number format. Changing the display format alone does not convert text into an Excel date value.

  • Look for blanks, text entries, or inconsistent date formats in the source column.
  • Check whether dates include times; the helper formula above is designed to include any time today.
  • Make sure the source range or Excel table includes every record and the helper field.
  • After correcting source values, recalculate if needed and refresh the PivotTable again.

Regional settings can affect how text such as 03/04/2026 is interpreted: it could mean March 4 or April 3. Use unambiguous dates when cleaning imported data, and verify a sample against the original source. If dates arrive as text from a CSV or system export, convert them to genuine Excel date values before relying on date filters.

Why the PivotTable may still show old or unexpected items

If the filter is set to TRUE but old records remain, first check whether the helper formula was filled down to every source row and whether the PivotTable was refreshed after recalculation. Then verify that the date values are genuine dates and that the PivotTable source includes the current data. A PivotTable based on a fixed range will not automatically include rows added below that range; using an Excel table as the source can make source expansion easier to manage.

If TRUE and FALSE appear as text labels or the filter list does not show TRUE, check that the helper formula was entered as a formula rather than copied as text, and look for errors in the helper column. If the PivotTable groups dates into years, quarters, or months, the helper field is still a separate field and can be filtered independently. You can also remove date grouping if you need to work with individual dates, but grouping is not required for the helper method.

Another common misunderstanding is that filtering the PivotTable changes the source data. It does not: the filter changes which records the report displays. The underlying rows remain in the worksheet, and clearing the filter restores the full report.

Choose a helper field, date filter, or timeline

Use the method that matches the job rather than treating every date control as equivalent:

  • Helper field: Best for a repeatable rolling window, particularly when you need exactly 30 calendar dates including today or need to handle timestamps consistently.
  • Date Filters > Between: Best for a fixed interval, such as a reporting period that must remain unchanged after it is saved.
  • Timeline: Useful for interactive browsing by date in supported desktop PivotTables. A timeline is a visual date selector, not a guarantee of a rolling last-30-days rule; the selected range is controlled by the user.
  • Manual item selection: Can work for a small list, but is easy to leave stale as new dates arrive and is less suitable for an ongoing report.

PivotCharts connected to the PivotTable generally reflect its filters. If a chart is intended to show the same rolling interval, verify that it is connected to the relevant PivotTable and that the helper filter is applied. A normal chart based on a separate range may not follow PivotTable filters automatically.

Frequently Asked Questions

Does Excel have a built-in PivotTable filter for the last 30 days?
Standard PivotTable date menus commonly provide fixed date ranges and calendar-based choices, but they may not provide a rolling last-30-calendar-days preset. A helper column using TODAY() is a reliable way to define and refresh that rolling window.

Should I use TODAY()-30 or TODAY()-29?
For 30 calendar dates including today, use TODAY()-29 as the starting date and include dates before tomorrow. TODAY()-30 through today covers 31 calendar dates when both endpoints are included. If you mean the prior 30 complete days, ending yesterday, define that period separately.

Will the PivotTable update automatically at midnight?
Not necessarily. TODAY() can recalculate when Excel recalculates the workbook, but the PivotTable must also refresh to reflect changed helper results. Refresh it manually, use Refresh All where appropriate, or configure refresh-on-open if that suits the workbook.

Can the filter include records with times?
Yes. The example formula uses a start date at or after today minus 29 and an exclusive end boundary at tomorrow. That includes timestamps throughout today while excluding future dates beginning tomorrow.

Why is TRUE missing from the PivotTable filter?
Check that the helper formula was filled down, the helper column is part of the PivotTable source, and the PivotTable has been refreshed. Also confirm that the formula returns Boolean TRUE/FALSE values rather than text or errors.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.