Fields
In the pivot table shown, there are four fields in the source data: Date, Sales, Week, and Filter. Filter is a helper column with a formula flagging the last 4 weeks. Week is a helper column that returns the Monday for any given date (for convenience only). Sales is the value field, and Week has been added as a Row field to group by week:
Formula
The formula used in D5 (Week), copied down, is: This formula returns the Monday of week for any given date. The formula used in E5 (Filter), copied down, is: This formula checks for dates in the last n weeks. It returns TRUE when a date is in the last 4 complete weeks, and FALSE if not. See the link for a full explanation.
Customizing week count
To customize the weeks displayed, adjust the Filter formula as needed to increase or decrease days. For example, to display the last 6 weeks (42 days), use:
Steps
Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
