How to Group Dates in a Pivot Table [Step by Step Guide]

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.

One of my favorite feature of a pivot table is to group dates.

With this, you can instantly create insightful and ready to present reports. When you work with a data where you have dates, 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 quick and easy to do.

And in today’s post, I’d like to show exact steps to group dates in a pivot table using different basis [Year, Quarter, Month, and Time].

Groups Dates in a Pivot Table by Month

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

  • Select any of the cells from the 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.

Create a Groups of 7 Days to Create Weeks

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.

Create a 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.

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

Create a Summary using a 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.

By 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

Conclusion

Which pivot table feature you love the most? 

Please share your views in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.

More Pivot Table Tips

  1. Apply Conditional Formatting to a Pivot Table: It will highlight city which has the highest amount and a city [...]
  2. Automatically Update a Pivot Table Range: The point is, you need a method to update source range automatically when [...]
  3. Pivot Table Timeline in Excel: A timeline is a new feature introduced in Excel 2013 and the good news is you can use it [...]
  4. Ranks in a Pivot Table: By using a pivot table, you can summarize your data in an understandable manner [...]
  5. Refresh all Pivot Tables: t saves a lot of time because once you create a pivot table, you just need to refresh it [...]
  6. Running Total in a Pivot Table: One of the things which I like about a pivot is we can easily add running total into it [...]

Content Protection by DMCA.com
2017-07-25T00:05:21+00:00
  • Eric Iso says:

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

  • Muhammad Fathy says:

    Stunning Trick, Many thanks

  • Vishesh Kumar says:

    nice info…..

  • mithun says:

    Nice information

  • Sandip says:

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

    • Puneet Gogia says:

      Thanks Sandip for your words.

  • >