How do I filter a pivot table by a date range?

Question

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:

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:

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.

Answer 1

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.

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...

Answer 2

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.