Grouping dates in the Pivot table in Google Sheets helps us create monthly, quarterly, and yearly summary reports.

Suppose you have your day-to-day expenses entered in a Google Sheets file.

You can group the date column in that statement to analyze your expenses, such as how much you spent monthly, in which month you were out of control in spending, etc.

One of the benefits of groping dates in the Pivot table is you don’t require the ability to use functions such as Query of Sumif for the same purpose.

Even a novice Google Sheets user can quickly group dates using the Pivot table in Google Sheets.

How to Group Dates in Pivot Table in Google Sheets

Suppose you have the following sample data in a Google Sheets file.

How do you create a Pivot table out of it? That’s important before answering how to group dates in Pivot Table in Google Sheets.

Steps:

1. Select the data range A1:D9.
2. Go to the Insert menu Pivot table.
3. Select where you want your report to appear. If you select “New sheet” and click “Create,” the program will insert a new tab named “Pivot Table 1” with a skeleton of the pivot table on the top part.

Here you can decide what data you want in rows and columns and which field to calculate.

I have added the following.

Rows: Date

Columns: Item

Values: Qty. (Summarise by Sum)

What happens in the result?

If any items repeat on the same date in the source, they will be merged, and their quantity will be summed together.

So initial grouping has happened. Let’s group dates in the Pivot table by month, quarter, and year.

Finding the Menu Option (Command)

We already have a Pivot table report where the first column contains dates. We can group this column straightaway.

The date grouping in the Pivot table feature is hidden behind the right-click shortcut menu.

You won’t see it by right-clicking on anywhere on the Pivot table report or within any cell in the Sheet.

Then how to get it?

Go to any cell that contains a date in the to be grouped column of the Pivot table report.

As per our report, we can choose any cells in the range A3:A7 in the Pivot table report.

Then right click. You will see the option as per the screenshot below.

You will get plenty of date and time grouping options under this command.

Month and Year-Month Grouping in Pivot Table

To group dates by month, open the shortcut menu as explained above and select Create pivot date group > Month. It will group the dates by month.

Use it only to group dates by months when your data is constrained to one year, or you don’t mind if the same months in different years are grouped together.

For example, January 2018 and January 2019 will be grouped together irrespective of the difference in the years they fall.

So, usually, you don’t want that to happen. So I prefer to apply Create pivot date group > Year-Month.

I have got a detailed tutorial here that also includes a Query solution. Here it is – Month-Wise Pivot Table in Google Sheets Using Date Column.

Quarter and Year-Quarter Grouping in Pivot Table

Quarterly reports are part and parcel of financial statements.

There are four quarters in a year, and the months that fall in that group are as follows.

Follow the below steps to group dates by quarter using the Pivot table in Google Sheets.

Right-click on any date cells, and select Create pivot date group > Quarter or Year-Quarter.

If your dates are spread across years, use Year-Quarter.

Year Grouping

Finally, here is how to group dates by year in the Pivot table in Google Sheets.

By now, you may already know how to do this. Here you go!

Right-click on any date cells, and select Create pivot date group > Year.

That’s all. Thanks for the stay. Enjoy!

Related: Drill Down Detail in Pivot Table in Google Sheets [Date Grouping].