Change quarters in Pivot table when grouping

J

Jugglertwo

I want to change the quarters when I do pivot table grouping by dates.
Is this possible?
thanks!
Jugglertwo
 
D

Dave Peterson

You don't like the cutoffs that MS uses?

If that's the question, then I add an extra field to raw data that returns the
quarter (and year). Then use that in my pivottable.

I like this format:

FY2010Q01
or
2010-01
so that I can sort in nice ascending/descending order.

If the quarters start on the first of a month, then I use this formula to show
the fiscal year and quarter:

="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)
Where # represents the first month of the fiscal year.

So if the fiscal year starts on July 1st, then I'd use:
="FY"&YEAR(A1)-(MONTH(A1)<7)&"-Q"&INT(1+MOD(MONTH(A1)-7,12)/3)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Tables and Fiscal Year 2
pivot table data 2
Dates In Excel 5
First Pivot Table 4
Change Quarters in Pivot Tables when grouping dates 1
Dealing with a Pivot Table (when I don't want to) 0
Additional column formula in pivot table 0
Custom grouping in Pivot Tables 2

Top