How to Group Dates in a Pivot Table

HomePivot TableHow to Group Dates in a Pivot Table

By grouping dates in a pivot table, you can create instant reports.

Here’s the point: Let’s say you want to group all the dates as months instead of adding a different column in your data, it’s better to group dates.

It’s super easy. Now here’s the good news: Apart from months, you can use years, quarters, time, and even a custom date range for grouping. And today in this post, I’d like to show you the exact steps for this.

NOTE: Pivot Tables are one of the INTERMEDIATE EXCEL SKILLS.

Groups Dates in a Pivot Table by Month

Below are the steps you need to follow to group dates in a pivot table.

Group Dates In Pivot Table By Month
  1. Select any of the cells from the date column.
  2. Right-click on it and select group.
  3. You will get a pop-up window to group dates.
  4. Select “Month” in the group by option and then click OK.

You can also use the above steps to group dates in a pivot table by years, quarters, and days.

Weekly Summary

You can also create a group of 7 days to get a week-wise summary. Please follow the below steps to this.

  1. Select “days” option from the group by option.
  2. Enter 7 in a number of days.
    nt/uploads/2016/06/pivot-table-with-group-dates-in-pivot-table-by-week
  3. Click OK.

You can use the above steps to create a group of dates for any number of days and please note that the week created by the pivot table is not on the basis of Mon-Sun.

Hourly Summary

You also have an option to group dates by time period. Let’s say if you have dates with time and you want to get an hourly summary. Just follow these simple steps for this.

  1. Select any of the cells with a date.
  2. Right click on it and select group.
  3. Select hour from the group by option.
    nt/uploads/2016/06/group-dates-in-pivot-table-by-hour
  4. Click OK.
    nt/uploads/2016/06/pivot-table-with-group-dates-in-pivot-table-by-hour

You can also use above steps to group dates in the pivot table by minutes and seconds.

Custom Date Range Summary

In the below pivot table, you have dates ranging from 01-Oct-2014 to 31-Jun-2015.

Group Dates In Pivot Table By Custom

And you want to create a group of dates by month, but only for 6 months of 2015 and all the months of 2014 in one group. Well, you can also create a group of dates by using a custom range, follow these simple steps for this.

  1. Right-click on date column and select “group”.
  2. Untick starting and ending date in auto option. And, enter your custom dates.
  3. Select month from by group option.
    nt/uploads/2016/06/group-dates-in-pivot-table-with-custom-dates
  4. In the end, click OK.
    nt/uploads/2016/06/group-dates-in-pivot-table-by-custom-dates

In above pivot, now you have dates in a group by months for the date range you have mentioned and rest of the dates which are not in the date range are in a single category.

Related: Excel Slicer

Group Two Different Fields

It happens sometimes that you need to use more than one time span to group dates in a pivot table. Let’s suppose, in the below pivot table, you want to group dates by quarters and months.

Group Dates In Pivot Table By More Than One Time
  1. First of all, select the group option from the right menu.
  2. After that, select quarters and months.
  3. In the end, click OK.
Use Two Time Span To Group Dates In Pivot Tables

Once you create more than one group for dates in the pivot table, you will also get an expanding and collapsing option.

Un-Grouping

If you want to get back your dates or want to ungroup dates you can do that with the “ungroup‘ option.

  1. Select a cell from the data column.
  2. Right-click.
  3. Select “Un-Group”.
Group Dates In Pivot Table Un-grouping

Sample File

download

More on Pivot Tables

  1. Conditional Formatting to a Pivot Table
  2. Automatically Update a Pivot Table Range
  3. Pivot Table Timeline
  4. Ranks in a Pivot Table
  5. Refresh all Pivot Tables
  6. Running Total in a Pivot Table

15 thoughts

Leave a Comment

Your email address will not be published.

  1. This is great, but I have a challenge. I have dates spanning over more than 12 months and when grouping by month, it combines the details for a month the 2 years into 1 row. How is this solved?

    Reply
  2. Is this for Excel 2016? When I right click the column I need to ungroup it doesn’t have group as an option. It’s messing up my pivot tables, I select to have the date and it breaks it up by quarters. I can’t see what I need to see and I need to send out this report like yesterday. 😬

    Reply
  3. Hi Puneet
    I loved your guidance on the Grouping Option in Pivot Tables

    Very clear explanations
    Neil

    Reply