I can't filter by dates in pivot.

Question

In a table I have a column of dates and in the table I can filter by months and years and last 30 days etc.. But when I put it in o a pivot table and but dates in to filters in pivotable fields when I try to filter it just list every date individually instead of being able to filter by months, years and last 30 days.
P.S. data is live, so when I refresh data it needs to all update.

Answer 1

Hi @Joshua Smith,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, when you drag a Date field into the Filters area of a PivotTable, Excel treats it like a list of individual items (each day is an item). In that position you typically only get the “checkbox list of dates”, not the “Last 30 days / This month” style experience you see in a normal table filter.

The suggested workaround for your concern is using a Timeline to filter date or put the Date field in Rows or Columns, then use Date Filters / Grouping. You can follow the detailed guidance in these articles:

• Create a PivotTable timeline to filter dates - Microsoft Support
• How to Use Excel Pivot Table Date Range Filter- Steps, Video (Note: I'm providing this information as a convenience to you. The sites are not controlled by Microsoft, and I can't make any representations regarding quality, safety or suitability of any software or information found there. Please make sure you completely understand the risk before retrieving any suggestions from the above link) 

I hope this helps you move forward smoothly. If you have any questions or need further assistance, please feel free to reach out again on this post so I can continue to support you.  

Thank you for your patience and understanding. Looking forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

Answer 2

Try: right-click and create grouping on your row field.

When creating the date field, Excel should create multiples - like "Date" and "Date (Month)" but then if one is removed, you need to recreate the grouping manually, otherwise it isn't available.