Forum Discussion
How to use start and end dates in a Pivot table instead of a timeline
It depends to a certain extent on how you intend that filtering to work. By that I mean, you could be wanting to filter on absolute dates; or you could be wanting to filter on extent of time between two dates, regardless of the absolute dates of the start and finish. And maybe there are some other possible combinations, but I'll use those two to illustrate at least two possibilities.
- If you are wanting to use a filter to have your Pivot Table summarize data only from March 1 to March 30 (or any other pairing of absolute dates) you could set up two pivot table filters, greater than one, less than the other.
- If you are wanting to use the dates to establish a 30 day interval regardless of when the interval starts, you could create another column that calculates that delta.
Now, if none of that makes sense (or you don't see how to do it), please upload a sample of your file so somebody here can see the actual layout of your data.
Here is a file.
Also here is what I am trying to do:
What I am trying to do is to allow others to modify the results. I just want them to select the start date and end date and refresh the pivot table for updated results.
In connection with another thread, I've come across the linked YouTube video which explains how to accomplish what you are trying to do. It's tricky, but effective.
https://www.youtube.com/watch?v=sHfWRb2yUrM
I'm going to confess my own ignorance here: I'd never seen that Timeline capability before. [I began using Excel (well, Lotus 1-2-3 actually] back in the late '70s, early '80s. Used to read the manuals, and was for the time a "power user", up until when I retired in 2002. Excel has introduced a LOT of new features since then, and Timeline (as you've deployed it here) is one of them.]
And I think it--Timeline-- looks amazing, and easy to use. And I'm going to be looking for opportunities to use it myself.
So although it might be easier to let users select a start and end date, I'm not convinced you'd be doing them any favors.
Unless, that is, one of your mandates is to be less flexible than the Timeline allows. You mention selecting a standard work week, Mon-Fri. IF you only want to display results for any given Mon-Fri period, I'd suggest using one or more of the date functions to create a "helper column" that reads from 1 to 52, so that the data for any given day gets funneled into the appropriate week, and use that WeekNum column as the filter your users can select.
I'm making a couple of assumptions here: (1) the actual spreadsheet you're working with contains far more granular data than you show in your example; (2) you're enough of a power user that you don't need more help in developing those supplemental columns that either Sergei or I have suggested.
Obviously, though, come back with further questions or comments as you have them.