Skip to main content

How To Group Date By Month, Year, half year or other specific dates In Pivot Table?

Author: Sun Last Modified: 2020-04-23

In Excel, if the data in a pivot table includes date, and have you tried to group the data by month, quarter or year? Now, this tutorial will tell you how to group date by month/year/quarter in pivot table in Excel.

Group date by month, quarter or year in pivot table

Group date by half year, week number or other specific dates in pivot table

Simple demo


Group date by month, quarter or year in pivot table

There is a Group function in pivot table, you can apply it to group data as your need.

1. Select the data range you need, and click Insert > PivotTable. See screenshot:

2. In the Create PivotTable dialog box, check the option you need in the Choose where you want the PivotTable report to be placed section. See screenshot:

3. Click OK to create the pivot table. And in the PivotTable Field List pane, drag the field to the areas you need, and make sure the Date label is in Row Labels list. (In Excel 2016 and later versions, it will automatically group the Date into Years and Quarters), see screenshot:

4. Then go to the pivot table, right click anywhere under the Row Labels head, and select Group. See screenshot:

5. In the Grouping dialog, select the grouping criteria from the By list box. Here I select Month and Years. See screenshot:

Tips: In this Grouping dialog box, you can also select the Months, Quarters, Years separately for grouping the date by month, quarter or year as you need.

6. Click OK to apply the function. Now you can see the data is grouped by month and year as following screenshot shown:


Group date by half year, week number or other specific dates in pivot table

With the above method, you can group date by month, year, quarter quickly, but, sometimes, you may want to group date by specific date, such as fiscal year, half year, week number and so on. The normal Group function will not support to deal with it.

If you have Kutools for Excel, with its PivotTable Special Time Grouping feature, you can quickly group date by fiscal year, half year, week number, day of week, half an hour or specific minutes as you need.

Tips:To apply this PivotTable Special Time Grouping feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools Plus > Pivot Table > PivotTable Special Time Grouping, see screenshot:

2. In the PivotTable Special Time Grouping dialog box, please do the following operations:

  • (1.) First, select the data range that you want to create a Pivot Table which grouped specific date;
  • (2.) From the Group By list box, choose the option that you need, in this case, I select Half year;
  • (3.) Then, select the date column you want to group by;
  • (4.) At last, choose a location where to output the Pivot Table, you can put the pivot table into a new worksheet or a cell of the current worksheet as you need.

3. After finishing the settings, please click Ok button, and a new Half year helper column is added at the right of the data range.

4. Then, click any cell in the Pivot Table to activate the PivotTable Fields pane, drag and drop the Half Year field to the Rows section, and then drag other fields to proper sections as you need. Now you will see the Pivot Table is created, and the dates are grouped by half year of each year. See screenshot:

Click to Download Kutools for Excel and free trial Now!


Simple demo

Group date by fiscal year, half year, week number or other specific dates in pivot table

Normally, you can group the pivot table by month, year, quarter quickly, but, sometimes, you may want to group data based on the fiscal year, half-year and other specific dates. In this case, Kutools for Excel's PivotTable Special Time Grouping utility extends the original Grouping function and supports more dates grouping. Click to download Kutools for Excel!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Super helpfull
Thanks
This comment was minimized by the moderator on the site
On dates columns I want no grouping watsoever. How to achieve that?
This comment was minimized by the moderator on the site
right click on the date it creates (like the year or the quarter when dropping the field on rows) and select ungroup
This comment was minimized by the moderator on the site
so simple but so effective! Exactly what I wanted to do.
This comment was minimized by the moderator on the site
I was searching on how to do that, thanks
This comment was minimized by the moderator on the site
Thank you so much!!! :D
This comment was minimized by the moderator on the site
Thank you very much...help me a lot!!!!
This comment was minimized by the moderator on the site
I have date wise sales data from 01-01-2015 to 0-04-2018. How can i group it as
1st to 10th of every month
11th to 20th of every month
21st to 31st of every month

Thanks.
This comment was minimized by the moderator on the site
Thanks, great help. Now it groups values from January 2018 in the sale column as January 2017. Is there a way to have the year included in the grouping?
This comment was minimized by the moderator on the site
The group dialog is multi select, make sure year and month are selected.
This comment was minimized by the moderator on the site
Really Helpfull. Thank You
This comment was minimized by the moderator on the site
Thank you for the help!!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations