Pivot Table Report Grouping Date Field in Microsoft Excel 2010

 

To make use of grouping the date field by quarter in pivot table report, we can use a combination of MOD, CEILING & MONTH functions to get the output.

Let us take an example:

Column A contains Dates & column B contains Sales.We need a formula to calculate the Quarter number in a Fiscal year. For example, Q1 will start from 1-Oct to 31-Dec, Q2 from 1-Jan to 31-Mar, Q3 from 1-Apr to 30-Jun, Q4 from 1-Jul to 30-Sep.

img1

  • In cell C2, the formula would be
  • =”Q”&(MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1)

img2

  • Copying down the formula in below shown range, we will get the desired result.

img3

To make use of Quarters in a Fiscal year, we need to create a pivot table report.

  • Arrange the fields as shown in below shown snapshot.

img4

  • To view the entire report quarter wise, you need to click on any cell in column A & right click
  • Select Expand/Collapse & then click on Collapse Entire Field

img5

  • You will be able to view the report quarter wise.

img6

In this way we can group in excel.



One thought on “Pivot Table Report Grouping Date Field in Microsoft Excel 2010

  1. I commonly refer to Quarters in my pivot tables but I use the CHOOSE function. The formula looks like this.
    =CHOOSE(MONTH(A2),1,1,1,2,2,2,3,3,3,4,4,4)
    Great post, I love this site!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>