Pivot Table Report Grouping Date Field in Microsoft Excel 2010

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-pivot-tables/pivottable-report-grouping-the-date-field-by-quarters-in-a-fiscal-reporting-year.html
SHARE




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.

Please follow and like us:
0


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 *

To avoid automated spam,Please enter the value *

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>