How to use start and end dates in a Pivot table instead of a timeline

Question

I have a date column in my Pivot table.&nbsp; I use a timeline to select the dates.&nbsp; However, this is a bit clumsy.I'd like to show fields for start date and end date.&nbsp; How can I then use those values as a filter in my pivot table?&nbsp;What I am trying to do is to allow others to modify the results.&nbsp; I just want them to select the start date and end date and refresh the pivot table for updated results.&nbsp;Attaching a file which shows what I am trying to do.

mathetes · Answer

nilanjenator&nbsp;&nbsp;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.&nbsp;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.

nilanjenator · Answer

mathetes&nbsp;In the timeline control, I select all days from Monday to Friday of a week.&nbsp;&nbsp;&nbsp;The question really is how to use variables in a pivot table, without the user having to select values in a timeline or slicer.&nbsp;I'll post a file later.

sergeibaklan · Answer

nilanjenator&nbsp;
As variant
Let say you have range of dates and source table

If range is not a table let add it to data model through PivotTable. Let build main PivotTable with adding it to data model.
&nbsp;
In data model add calculating column to main table

and use it as the filter.

nilanjenator · Answer

mathetes&nbsp;,&nbsp;SergeiBaklan&nbsp;&nbsp;&nbsp;Here is a file.&nbsp;&nbsp;&nbsp;Also here is what I am trying to do:What I am trying to do is to allow others to modify the results.&nbsp; I just want them to select the start date and end date and refresh the pivot table for updated results.

mathetes · Answer

nilanjenator&nbsp;&nbsp;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.]&nbsp;And I think it--Timeline-- looks amazing, and easy to use. And I'm going to be looking for opportunities to use it myself.&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;Obviously, though, come back with further questions or comments as you have them.

Forum Discussion

How to use start and end dates in a Pivot table instead of a timeline

6 Replies

Resources