We have a spreadsheet that tracks Customer Complaints. The complaints
are entered by date and other details, such as the category for the
complaint.

I have a pivot table set up with date groupings by year and month. But
this gives the breaks according to the calendar year (Jan thru Dec).
Our fiscal year runs Jul 1 thru Jun 30.

Also, our Qrtr1 is Jul 1 thru Sep 30 and not Jan 1 thru Mar 31, so
grouping by quarter is also confusing to people.

One way to handle this would be to add a couple of columns to calculate
the Fiscal Year and Quarter. But I am not sure how to handle the
grouping would work if I do that.

Can I get set up a pivot table to actually use the rules of our Fiscal
Year, withour adding two columns to the data table?


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=561292

You can't change the pivot table's date grouping to adjust to your
fiscal year. If you add columns to the source data, and calculate the
fiscal year and quarter, you could add those fields to the pivot table,
and wouldn't need grouping.

DCSwearingen wrote:
We have a spreadsheet that tracks Customer Complaints. The complaints
are entered by date and other details, such as the category for the
complaint.

I have a pivot table set up with date groupings by year and month. But
this gives the breaks according to the calendar year (Jan thru Dec).
Our fiscal year runs Jul 1 thru Jun 30.

Also, our Qrtr1 is Jul 1 thru Sep 30 and not Jan 1 thru Mar 31, so
grouping by quarter is also confusing to people.

One way to handle this would be to add a couple of columns to calculate
the Fiscal Year and Quarter. But I am not sure how to handle the
grouping would work if I do that.

Can I get set up a pivot table to actually use the rules of our Fiscal
Year, withour adding two columns to the data table?




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

That was the only way I could figure out. Even though this is a fairly
simple approach, I was hoping for a more straight forward method.

If anyone out there has somehting more straight forward than the
following, let me know.

The date is in column C when importing and the data starts in row 2 and
our Fiscal Year starts on July 1.

Formula for Fiscal Year is:
="F" & IF(MONTH(C2)<7,RIGHT(YEAR(C2),2),RIGHT(YEAR(C2)+1, 2))

Formula for Fiscal Period is:
="P" & IF(MONTH(C2)<7,MONTH(C2)+6,MONTH(C2)-6)


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=561292