How can I group a pivot table into weeks or months?

Question

In Microsoft Excel, in a pivot table, you can right click on the dates - while within the pivot table, on a date and select group (from the drop down menu) - then you can choose different groupings, week, month, year, etc.

Then, when in the pivot table field list, for a row or column you can choose the date by week, month, year, etc.

Anyone know if you can do this in Google Spreadsheets?

The Google site for spreadsheets only covered very basic instructions. I was going to ask there, but I found this site while searching and thought I would try here first.

Answer 1

I don't, except I suspect you are out of luck for the time being at least. However, even in Excel I have often found it as, or even more, useful to perform such grouping in the base data rather than in the pivot table. For me the summary provided by the table is often a prelude to drilling down for further detail and rather than end up then with many different extracts I find it much easier to filter the source data, which I have already formatted to suit me.

On that basis a hack of sorts may serve for you. Assuming data as in D:E in the image, I added three columns with formulae:

• A: =year(D2)
• B: =month(D2)
• C: =weeknum(D2)

To reduce the need for recalculation I usually then convert the results to values.

ColumnsA:C can then be included in the pivot table and, unlike Grouping, the components of the date can be split between rows and columns in the pivot table:

Answer 2

You can now do that in Google Sheets, similar to what you're describing for Excel: Add dates as rows/columns, right click on a value and select "Create pivot date group".

This works for months (and year, etc), but unfortunately not for weeks. For that you still have to resort to something like pnuts is suggesting.

Answer 3

In Google Sheets, take a look at ISOWEEKNUM(). I created an extra column with the week number for each date, which I could then use in the pivot column under month for the same date to expand each month to week.

Answer 4

You can create a "pivot table group", from seconds to years, unfortunately, week is not available, for week grouping do as follows:

1. format the dates as numbers.
2. go to create dynamic group of "numbers"
3. put 7 as the interval
4. Optional: if you want a specific starting/ending date use numbers ex: 09/15/2020 = 44088

when the groups are formed, Google Sheets will recognize them again as dates.

sheet example: https://docs.google.com/spreadsheets/d/18zROiQ2qfBJn8drqpd9oJG8gPK1FIVdoPFVb1ErkWI4/edit?usp=sharing