J Jugglertwo Mar 10, 2010 #1 I want to change the quarters when I do pivot table grouping by dates. Is this possible? thanks! Jugglertwo
I want to change the quarters when I do pivot table grouping by dates. Is this possible? thanks! Jugglertwo
D Dave Peterson Mar 10, 2010 #2 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)
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)
