Group Dates in a Pivot Table

A pivot table is full of awesome features. It doesn’t matter how big your data is, there is no problem for pivot table to handle it.

Creating a group of dates in a pivot table is one those features. With this, you can instantly create insightful and ready to present reports.

When you work with a data where you have dates in it, the best thing to summarize it in months, years, or quarters.

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

It’s easy, quick and a big time saver.

And in today’s post, I’m going to show you how to group dates in pivot table using different basis [Year, Quarter, Month, and Time].

So let’s get started.

Groups Dates by Month

To combine dates in your pivot table please follow these simple steps.

  • Select any of the cells from date column.
  • Right-click on it and select group.
Right Click To Group Dates In Pivot Table
  • 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.

Groups Dates by Week

You can also create a group of 7 to get a week wise summary. Please follow below steps for 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.

Groups Dates by Time

You also have an option to group dates by time periods. Let’s say if you have dates with time and you want to group dates to get an hourly summary.

Just follow these simple steps to do that.

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

Groups Dates by Custom Date Range

You can also create a group of dates by using a custom range. Just follow these simple steps for this.

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

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. 

Group Dates In Pivot Table By Custom
  • 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
  • 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.

Groups Dates by a Two 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
  • All you have to do is select group from the right menu.
  • Select Quarters and Months.
  • 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-group

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

Download this sample file from here to learn more.

Conclusion

Using this option for grouping dates in a pivot table is a time saver. You don’t have to insert those extra columns in your source data.

Want to know the best part?

You have different options to combine dates and create a summarize pivot table.

I hope this pivot table tip will help you get better at Excel.

😃

Now tell me one thing. Do you use it in your work or not? Or you never heard about this. Please share your views in the comment section, I’d love to hear from you.

And, please don’t forget to share this tip with your friends.

Must Read Next

  1. Connect Slicer With Multiple Pivot Tables
  2. Pivot Table from Multiple Worksheets
  3. Pivot Table Timeline
  4. Pivot Table Using Multiple Files
  5. Add Running Total in a Pivot Table

  • Sandip

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

    • Puneet Gogia

      Thanks Sandip for your words.

  • mithun

    Nice information

  • Vishesh Kumar

    nice info…..

  • Muhammad Fathy

    Stunning Trick, Many thanks

    • Puneet Gogia

      Welcome.