Pivot Tables and Fiscal Year
I use a helper column in the data table (before the pivottable is built) to show
the year and quarter.
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)
RE Miller wrote:
When grouping dates by quarters in a pivot table, is there any way to modify
which months are considered Q1, Q2, etc? I'd like Q1 to reflect dates from
July-September rather than Jan-March. Is this possible?
--
Dave Peterson
|