How do I filter a Pivot Table on a Date Range? | Power Pivot | Excel Forum • My Online Training Hub

How do I filter a Pivot Table on a Date Range?

Emily Parsons

New Member

Members

Forum Posts: 2

Member Since:
July 24, 2014

Offline

August 25, 2017 - 2:23 am

I have a large (fairly) table of sales transactions from my business which I analyze with pivot tables. I have created columns in my data table for Day, Month and Year from the Sales Date, and use slicers for those to drill down the data each month. I look at data such as gallons sold, revenue and quantity of each varietal sold each month (I own a winery.) Some months I don’t take inventory until the second or third day of the month, and I’d like to be able to look at the data for a specific date range such as July 2 - July 31, rather than using the July slicer. I can’t figure out how to do this. Any tips?

Thank you,

Emily Parsons

Mynda Treacy

Admin

Forum Posts: 732

Member Since:
July 16, 2010

Offline

August 25, 2017 - 9:19 am

Hi Emily,

If you put the Date field (i.e. the field that contains the whole date, not just the day), in the Rows area or Filters are of your PivotTable you can filter for dates between a range. Click on the filter button for the field > Date Filters > Between and specify the dates you want.

Let me know if you get stuck.

Mynda

Emily Parsons

New Member

Members

Forum Posts: 2

Member Since:
July 24, 2014

Offline

August 27, 2017 - 10:38 am

Thank you Mynda. I will try it. At this point the Date is structured with a time stamp as well which I haven't easily gotten rid of so I wind up with a huge data set of to choose from. I'll mess with the date field more to clean it up and try that again.

Mynda Treacy

Admin

Forum Posts: 732

Member Since:
July 16, 2010

Offline

August 27, 2017 - 8:29 pm

Hi Emily,

Power Pivot ignores the time portion of the date. Just format the date column as data type; 'Date', and it should be fine.

Mynda