Pivot Table Last 4 Weeks: How to Filter

To show only the last 4 weeks in a pivot table, you add a date field to the Filters area and set it to a custom “Last 4 weeks” range, or you create a calculated field that flags dates within the last 28 days. This article explains both methods, how to handle week definitions, and common pitfalls when working with rolling date ranges in Excel and Google Sheets.

Formula

The core calculation is simple: the start date of your last 4 weeks is today minus 28 days (4 weeks × 7 days). In Excel, you can write this as:

=TODAY()-28

In Google Sheets, the same formula works. If you want a fixed reference date instead of today, replace TODAY() with a cell containing your end date. The variables are:

• End date – the anchor date (usually today)
• Weeks back – number of weeks to include (4 for last 4 weeks)
• Start date – the earliest date included, calculated as end date minus (weeks × 7 days)

Worked Example

Suppose today is May 15, 2024, and you want the last 4 weeks. Using the formula:

1. End date = May 15, 2024
2. Weeks back = 4
3. Start date = May 15, 2024 − (4 × 7) = May 15, 2024 − 28 days = April 17, 2024

So your filter range is April 17, 2024 through May 15, 2024. The calculator above gives the same result: start = 2024-04-17, days = 28.

Why “Last 4 Weeks� Is Tricky in Pivot Tables

Pivot tables group dates by day, month, quarter, or year, but they don’t have a built-in “rolling last 4 weeks� option. The built-in date filters in Excel (like “Last Month� or “This Week�) are based on the calendar, not a rolling window. For example, “Last Week� means the previous calendar week (Monday–Sunday), not the last 7 days from today. This is fine if your reports align with calendar weeks, but if you need a true rolling 28-day window, you must create a helper column or use a custom filter.

Method 1: Add a Helper Column for the Date Range

The most reliable way is to add a column to your source data that flags whether each date falls within the last 4 weeks. Then you drag that column into the pivot’s Filter area.

1. In your source table, add a new column titled “Last 4 Weeks� (or similar).
2. Enter a formula like: =IF(AND(A2>=TODAY()-28, A2<=TODAY()), "Yes", "No") where A2 is the date cell.
3. Copy the formula down for all rows.
4. Refresh your pivot table (right-click → Refresh).
5. Drag the new field into the Filters area and set it to “Yes�.

This method updates automatically every time the pivot refreshes, because TODAY() recalculates.

Method 2: Use a Custom Date Filter (Without Helper Column)

If you don’t want a helper column, you can apply a custom filter directly on the date field in the pivot:

1. Click the drop-down arrow on the date field in the pivot table.
2. Choose Date Filters → Custom Filter (or Between).
3. In the dialog, set the condition to is after or equal to and enter =TODAY()-28.
4. Set the second condition to is before or equal to and enter =TODAY().
5. Click OK.

This works in both Excel and Google Sheets, but the exact menu wording may differ slightly. In Google Sheets, you use “Filter by condition� → “Greater than or equal to� and then a second condition.

Common Mistakes and How to Avoid Them

• Using “Last Monthâ€� instead of rolling weeks – “Last Monthâ€� is a calendar month, not 28 days. If your data spans mid-month, you’ll get the wrong period.
• Forgetting to refresh – Pivot tables do not automatically recalculate when your source data changes. Always refresh (right-click → Refresh) after updating the data.
• Time component in dates – If your date column includes timestamps (e.g., 5/15/2024 14:30), TODAY() is midnight. Use =TODAY() and =TODAY()-28 but be aware that dates with times after midnight may be excluded if they are on the boundary. To avoid this, use =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())) for the end date, or strip times using INT().
• Week definition differences – Some businesses consider weeks starting Monday, others Sunday. The 28-day rolling window ignores weekdays; if you need Monday-to-Sunday, you need a different approach (see below).

How to Handle “Last 4 Complete Weeks� (Monday–Sunday)

If you need the last 4 full calendar weeks (e.g., Monday through Sunday), the calculation is different. You need to find the most recent Monday that is before or on today, then go back 4 weeks from that Monday.

In Excel, you can get the current week’s Monday with: =TODAY()-WEEKDAY(TODAY(),2)+1 (where WEEKDAY with return type 2 gives Monday=1). Then subtract 28 days for the start: =TODAY()-WEEKDAY(TODAY(),2)+1-28. The end date is the Monday before today (or today if today is Monday).

In Google Sheets, use: =TODAY()-WEEKDAY(TODAY(),2)+1 for the current Monday. Then your range is from that Monday minus 28 days to that Monday.

Frequently Asked Questions

Why does my pivot table show “Last 4 weeks� but includes more than 28 days?

If you used the built-in “Last Weekâ€� or “Last Monthâ€� filter, it’s based on calendar periods, not a rolling 28-day window. For example, “Last Monthâ€� always shows the entire previous month, which could be 30 or 31 days. To get exactly 28 days, use the custom filter with TODAY()-28 as described above.

Can I make the last 4 weeks update automatically every day?

Yes, if you use TODAY() in your helper column or custom filter, the range updates when the pivot refreshes. However, if you open the file tomorrow, the pivot will still show the old data until you refresh. You can set the workbook to refresh on open (in Excel: Data → Queries & Connections → Properties → Refresh data when opening the file).

What if my dates are in a different time zone?

Excel and Google Sheets use your computer’s local date for TODAY(). If your data is in a different time zone, you may need to adjust the formula by adding or subtracting hours. For example, if your data is in UTC and you are in UTC+5, use =TODAY()-28 but be aware that the boundary may shift. In practice, it’s safer to use a fixed end date cell that you update manually if time zones cause issues.

How do I show the last 4 weeks as a grouped range in the pivot table?

After filtering, you can group the dates by right-clicking on a date cell → Group → Days, and set “Number of days� to 28. This groups all dates into 28-day buckets, but note that it will create a single group for the entire range, not separate weeks. To get separate weeks, group by “Days� and set number of days to 7, then filter to the last 4 groups.

Does this work in Google Sheets pivot tables?

Yes, the same principles apply. Google Sheets pivot tables also lack a rolling date filter, so you use a helper column or a custom filter condition. The formula for the helper column is the same: =IF(AND(A2>=TODAY()-28, A2<=TODAY()), "Yes", "No"). Then add that field to the filter area.

Related

• Mosquito Control for Homes and Yards: What to Buy, Use & Skip
• Cheapest 400 HP Sports Cars: Top Picks
• How to Evaluate a Gut Microbiome Test

in d | End date (today) | - | 2024-05-15 | 0
in n | Weeks back | weeks | 4 | 1
out start | Start date | - | d - n*7 | 0
out days | Days in range | days | n*7 | 0