Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 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!


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!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hilary · 4 months ago
    so simple but so effective! Exactly what I wanted to do.
  • To post as a guest, your comment is unpublished.
    one user · 10 months ago
    I was searching on how to do that, thanks
  • To post as a guest, your comment is unpublished.
    Ben · 11 months ago
    Thank you so much!!! :D
  • To post as a guest, your comment is unpublished.
    Nathália · 1 years ago
    Thank you very much...help me a lot!!!!
  • To post as a guest, your comment is unpublished.
    Khalid Shah · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Jeroen · 1 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Butchamon · 1 years ago
      The group dialog is multi select, make sure year and month are selected.
  • To post as a guest, your comment is unpublished.
    Shanika Jayathunga · 1 years ago
    Really Helpfull. Thank You
  • To post as a guest, your comment is unpublished.
    Jimmy · 1 years ago
    Thank you for the help!!
  • To post as a guest, your comment is unpublished.
    Jessica Umanzor · 1 years ago
    Very useful instructions. Thank you!
  • To post as a guest, your comment is unpublished.
    Tasha · 1 years ago
    These instructions were incredibly helpful! Thank you!