How does one filter pivot tables by date range in Google Spreadsheets?

Question

I have a Google spreadsheet that keeps track of time spent on graphic design projects. I am trying to run pivot table reports to understand where we can optimize projects. In these pivot tables I would like to filter by a date range. Example: all of January (1/1/2014 -1/31/2014) or by week 1/1/2014 - 1/7/2014. When filtering by date range the grand totals should adjust. The pivot table looks like this:

          Project 1   |   Project 2   |   Project 3   |   Project 4   |  

1/1/2014       2             4                2               0  

1/2/2014       3             9                0               8

1/3/2014       5             2                2               4

1/4/2014       3             1                0               2
------------------------------------------------------------------------
Grand Total    13            16               4               14

I can run a filter from the pivot table dialogue box but I have to individually check or uncheck each date. This doesn't seem very efficient.

Is there a way to accomplish setting date ranges in the pivot table dialogue box that I'm not aware of? If not is there a formula or script that can be added?

Answer 1

Excel allows a Grouping facility for what you require but I see no equivalent in Google Docs. A workaround is to add flags in your source data. I did so in Columns B and C with:

=MONTH(A2)    and    =WEEKNUM(A2)    respectively.  

then included these as rows in the pivot.

In the image months 1 and 3 (I changed the dates from those in your example) have been rolled-up but not month 4:

Answer 2

Another option is to sort your spreadsheet by date, and then select (click and drag) the rows for the range of dates you want and then create the pivot table. The pivot table will then just use the data range you selected on the spreadsheet.

Answer 3

Now you can do it when you add a Filter with condition Is between and enter your date manually.

In my case 2018-12-01 and 2018-12-31 for the month December.