Filtering Dates in Excel PivotTables: How to Isolate Dates from the Past 4 Weeks

Filtering dates from past weeks is pretty useful when you want to compare the trend of a particular weekday or weekend.

Here's an example of a Pivot Table, and I want to automatically filter the day in the past 4 weeks to compare the trend across the days whenever I update the raw data and refresh it.

Here’s the sample raw data of stores sales for the above Pivot Table from June 2024 until August 2024.

To make the pivot table to filter the days in past 4 weeks automatically, this is how I do it:

Basically I want to compare the days across yesterday; for example if yesterday was Wednesday, I want to compare among the Wednesdays in the past 4 weeks.

At the raw data:

Add an additional column to check on whether the date is the particular day in past 4 weeks.

The Excel formula goes like this:

=OR([@Date]=TODAY()-8,[@Date]=TODAY()-15,[@Date]=TODAY()-22,[@Date]=TODAY()-29)

*Note that "[@Date]" is my date column header, remember to replace it with your date column header.

In this formula, I’m comparing yesterday’s data with the data of the same day in the past 4 weeks.

For example, yesterday was Wednesday, then I will compare with the Wednesdays in the past 4 weeks.

TODAY()-8	Yesterday’s day in last week
TODAY()-15	Yesterday’s day in last 2 week
TODAY()-22	Yesterday’s day in last 3 week
TODAY()-29	Yesterday’s day in last 4 week

The formula would output a TRUE, FALSE column like below. If the date is within the past 30 days period, then it will be TRUE and vice versa.

Add a pivot table and add a slicer or filter for the "Past 4wks" column.

Filter the "Past 4wks" to be TRUE, then it will only filter the days in past 4 weeks every time the raw data is updated and refresh!

I have attached the sample file with a set of raw data and pivot table for your reference, in case you want to see how I did it.

Finally, hope this simple article can be helpful to you and have fun with the data you're working on~

Days in Past 4 weeks Sample.xlsx

Download XLSX • 63KB