Custom Date Range Search within Pivot Table

[kjnjanx]

Hello,

I'm wondering if anyone knows of a simple solution that would allow me to enable a user to pick a date range from a drop down (or calendar), and then have that filter the attached pivot table data.

For example, I would like a user to be able to select a "START DATE" and "END DATE", and then have the data update accordingly. If I went in and selected "January 3 - February 7th", I would then like the data to update with just the selected time frame.

Is there a combo box or some sort of form that would allow this functionality? I'm trying to outline a dashboard within Excel and want this to be an easy drop down/selection. I know that I can add slicers into the pivot table, but they do not allow me to necessarily specify any random date range.

Any advice or guidance would be greatly appreciated!

Thank you!

[dflak]

I can think of two ways to do this. One is somewhat complicated and involves setting the pivot table filters using VB.

The other is easy to implement and does not involve VB. First, I recommend that for whatever data range you are using for the pivot table, convert it to an Excel Table. Excel Tables "know" how big they are so when you add rows, all you have to do is refresh the pivot table. Excel tables also "remember" and copy down formulas automatically.

Let's say that the Start Date is Cell B1 on sheet 1 and End Date is Cell B2 on Sheet1. I recommend you put data validations on these cells at least to limit user input to dates and possibly dates that you know make sense and even that the end date must be greater than or equal to the start date.

Anyway, in the table that is the source data for to the pivot table add a helper column. I'll assume the date in a column called Date. Call the helper column "In Date Range" or whatever you like and use the Formula = AND([@[Date]]>=Sheet1!$B$1,[@[Date]]<=Sheet1!$B$2). This expression evaluates to TRUE if the date is in the range of the dates specified. Use this as a filter on the pivot table. The column in the data table can be hidden if you don't want to see it.

Here is more information on Excel Tables: http://www.utteraccess.com/wiki/Tables_in_Excel
And here is information on Data Validation: http://www.utteraccess.com/wiki/Data_Validation

[kjnjanx]

Thank you for your reply! I think this might work, provided I follow everything correctly. I'm very much still learning about all of this so I apologize for the follow up questions.

I should have explained a bit more upfront. In my table with my data, every record/row has an associated date (in other words, the date the customer reached out to us). There might be 4 records on January 3rd, 6 on January 4th, and so on. My hope is to be able to create something that will allow me to specify the date range I want to filter to, and then have the pivot table only include those dates/records.

Would your response still work for this? Do I need to add an additional column for date(s) - start or end? I only have the 1 column with the date of initial contact right now.

Thanks again.

[dflak]

If you can attach a sample workbook, I can show you what to do. It works better when I have something concrete to look at. I do think you have the right idea.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[kjnjanx]

Thank you for your help!

I have attached a spreadsheet of dummy data. Ideally, I would just like to have a spot in the top left corner of the dashboard worksheet (where the slicers sit currently), that will allow me a drop down feature in which I can pick my START and END dates to filter.
Right now, it can be sliced by month, and then associated days in another slicer. However, that doesn't give me a true date range filter as it just picks up whatever 'days' I select and attributes them to any month selected.
I'm hoping to be able to pick any day (in any month) as a start date, and any day (in any month) as an end date - and then have the pivot table and associated data populate for the selected date/calendar range.

I hope I uploaded this correctly and explained well enough.

Thanks again - I truly appreciate it!

[dflak]

The only date I can see is Lead Date in Column X. I'll assume that's what you want.

[kjnjanx]

Yes, sorry, that is correct. What I mean by Start and End date is more so referring back to the range itself. So if I selected January 3rd (start) - February 9th (end), I would pull the data of anyone who had the lead date between that time frame.

[dflak]

This is what I did.

I converted the data range to an excel table and I left it named Table1.

I noticed that some of the "dates" in Column X were real dates and some were strings that looked like dates. So I added a helper column called Real Date with the formula: =IF(ISNUMBER([@[Lead Date]]),[@[Lead Date]],DATEVALUE([@[Lead Date]])) - this leaves the real dates as real dates and converts the strings into real dates.

On the dashboard I set up a place in rows 37 & 38 to enter the start date and end date. I added data validation:
-Cell B37 will accept any date later than or equal to 1/1/2019.
-Cell B38 will accept any date later than the date in cell B37.

I gave these cells names: Start_Date and End_Date respectively. If you want to move these cells to another location CUT and paste them and the spreadsheet will keep up.

Then back in Table1, I have another helper column called Use Date with the formula: =AND([@[Real Date]]>=Start_Date,[@[Real Date]]<=End_Date) - this is true of the date is between the start date and end date.

The pivot tables were still all "connected" to the old range. However, you cannot change the source of a pivot table if there are slicers that link that table to other tables. So I removed ALL the links to other tables from the slicers. You will have to "reconnect" them.

I did set the source of all the pivot tables to Table1.

I added one more slicer to the table on the Dashboard sheet. to filter it only for dates in the date range. Use TRUE to see what is in the date range, FALSE to see what is out of the date range or Both to see all the data.

[kjnjanx]

Thank you so much!

After I set the Start/End dates, do I need to "Refresh" the pivot table to have everything update? It doesn't appear to update automatically depending on the dates I input unless I do that.

[dflak]

I threw in some code that will refresh the pivot table whenever either of the dates are changed. You still have to reconnect the slicers.

[kjnjanx]

Oh, you are the best! That is helpful!

One more question and then I'll hopefully stop asking questions.

Is there any way to make sure the 'Use Date' slicer always defaults and filters to true ONLY? I don't need to see what's out of the date range ever, only what is selected within the parameters. I noticed "FALSE" goes away in the filter if you put the date range out far enough since they all are True. But, if I were to re-set the end date to a date that wouldn't include everything, FALSE re-appears but it doesn't auto filter to only TRUE.

[kjnjanx]

Also, I have noticed that after saving and opening the spreadsheet, I am always given an "error in loading DLL" message. Do you know what would cause that?