3

My client has a pivot table and he'd like to be able to filter by date range. The data has start dates and end dates: enter image description here

He'd like to be able to put in a filter for 10/1/2020 and have the pivot table also show rows with a start date past that date (so 11/1/2020, 10/1/2023, etc). The default just lets him select individual dates:

enter image description here

I tried to insert a timeline, but I get this error: "We can't create a Timeline for this report because it doesn't have a field formatted as Date." Columns E & F in the data are both formatted as date, so I don't know why I'm getting this error. Of course, I'm not sure whether a Timeline is even the solution to this problem, but I'd love to know how to solve this (with or without a Timeline). Thanks!

Oh, and bonus question: He wants the pivot table to show the date for each row. I can do it with an Index/Match formula, but I can't figure out how to put it in the Pivot table itself. And if additional columns are added to the pivot table then my index/match gets overwritten.

1
  • Use Power Query. It'd be much more flexible. Nov 5, 2020 at 17:08

2 Answers 2

2

Upon creating the pivot table table, select the "Add the data to the data model" check box. This should allow you to easily add the dates to your pivot rows, like this, which has a design set to Tabular format.

enter image description here

Then, in the pivot table start date field, you could either select specific dates (which isn't what you're looking for), or can use the "Date Filters" option and select "after" for picking your date. Here I selected after or equal to 10/1/2020...

enter image description here

1
  • That's perfect!!! Thank you so much! It worked exactly how I wanted, thank you!
    – OzzyKP
    Nov 5, 2020 at 21:30
0

Those values are not valid dates, they are text formatted as dates. I put your dates into excel as below and formatted them to show "dd-mmm-yyyy" and they remain just as text. In the next column over I have proper dates with the same format.

Data entry on dates is often a problem, everyone has a different way they like to put dates in. I like to stick to the impossible to confuse international standard of "yyyy-mm-dd" when I enter dates (eg. "2020-10-1"), Excel usually gets it right.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.