Pivot Table Grouping or Calculated Field

[DCSwearingen]

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]

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]

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

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

The date is in column C when importing and the data starts in row 2 an
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