How 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.

Different Ways to Group Dates in a Pivot table

Below are some of the useful ways for grouping date and time values in a pivot table to make instant reports.

Let's get started...

1. Groups Dates in a Pivot Table by Month

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

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

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

excel pivot tale tips to make you master this year

2. Groups of 7 Days to Create Weekly Summary

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

  • Select “days” option from the group by option.
  • Enter 7 in a number of days.
Pivot Table With Group Dates In Pivot Table By Week
  • Click OK.

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

3. An Hourly Summary if You Have Time with Dates

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.

  • Select any of the cells with a date.
  • Right click on it and select group.
  • Select hour from the group by option.
Group Dates In Pivot Table By Hour
  • Click OK.
Pivot Table With Group Dates In Pivot Table By Hour

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

4. Creating a Custom Date Range Summary

In 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 months...

...but...

...only for 6 months of 2015 and all the month 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.

  • Right-click on date column and select “group”.
  • Untick starting and ending date in auto option. And, enter your custom dates.
  • Select month from by group option.
Group Dates In Pivot Table With Custom Dates
  • In the end, click OK.
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.

5. Grouping 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 below pivot table you want to group dates by quarters and months.

Group Dates In Pivot Table By More Than One Time
  • First of all, select group from the right menu.
  • After that, select Quarters and Months.
  • 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 “ungroup‘ option.

  • Select a cell from data column.
  • Right-click.
  • Select "Un-Group".
Group Dates In Pivot Table Un-grouping

Sample File

In the End,

Using the option to group dates in a pivot can be a big time saver.

You don’t need to insert those extra columns in your source data. 😍

And the best part?

You have different options to combine dates. I hope this tip will help you get better at Excel…

…but now tell me one thing.

Are you going to try this option?

Content Protection by DMCA.com
2018-12-06T07:48:05+00:00

10 Comments

  1. Eric Iso 15 Jul, 18 at 8:23 am - Reply

    Thanks for sharing.
    My favorite part of pivot table is “show report by filter pages”.

  2. Muhammad Fathy 29 Jul, 17 at 8:07 pm - Reply

    Stunning Trick, Many thanks

    • Puneet Gogia 11 Aug, 17 at 1:08 pm - Reply

      Welcome.

  3. Vishesh Kumar 27 Apr, 17 at 1:27 pm - Reply

    nice info…..

  4. mithun 24 Apr, 17 at 12:43 pm - Reply

    Nice information

  5. Sandip 16 Jun, 16 at 12:34 pm - Reply

    Really useful information presented in a crisp way. Thank you.

    • Puneet Gogia 16 Jun, 16 at 12:45 pm - Reply

      Thanks Sandip for your words.

Leave A Comment