9

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?

Improve this question

3 Answers 3

Reset to default
4

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:

enter image description here

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.