How To Group Date By Month, Year, half year or other specific dates In Pivot Table?
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
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
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...
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!
