You can group the dates in a pivot table in different increments, such as by year and quarter.

However, the grouping options are based on the calendar year, and there are no options for grouping pivot table dates by fiscal year. If you want to group the dates by your company’s fiscal year, which starts in July, there’s no built-in way to do that.

Fiscal Year Workaround

Because there’s no built-in option to group pivot table dates by fiscal year, you’ll have to use a workaround solution. In the pivot table source data, you can add a column with a formula that calculates the fiscal year, and then add that field to the pivot table.

For example, if your fiscal year starts in July, use the following formula to calculate the fiscal year, where the date is in cell A2:

=YEAR(A2)+(MONTH(A2)>=7)

The formula calculates the year of the date in cell A2. Then, it calculates the month of the date in cell A2, and checks to see if the month number is 7 or higher. If the month is less than 7, then zero will be added to the year, otherwise 1 will be added.

In the screenshot below, you can see the fiscal year formula for dates in June and July.

• The year for all the dates is 2010.
• For the June dates, the month is 6, which is less than 7. So, zero is added to the year, and the fiscal year is 2010.
• For the July dates, the month is 7, so 1 is added to the year, and the fiscal year is 2011.

Fiscal Quarter Workaround

If you also want to group the pivot table dates by the fiscal quarter, you can add another column to the pivot table source data. Use this formula to calculate the fiscal quarter, if the fiscal year starts in July.

=CHOOSE(MONTH(A2),3,3,3,4,4,4,1,1,1,2,2,2)

The Choose formula calculates the month of the date in cell A2. Based on the month number, the formula selects the appropriate fiscal quarter number from the numbers that follow. For example, if the month is June, the month number is 6. In the Choose formula, the sixth number is 4, so June is in fiscal quarter 4.

In the screenshot below, you can see the fiscal quarterformula for dates in June and July.

• For the June dates, the month is 6, and the sixth month in the Choose formula is 4, so June is in fiscal quarter 4.
• For the July dates, the month is 7, and the seventh month in the Choose formula is 1, so July is in fiscal quarter 1.

Add the Fiscal Dates to the Pivot Table

If you had grouped year and quarter dates in the pivot table, ungroup them and remove them.

Refresh the pivot table, and add the fiscal year and fiscal quarter fields to the Row Labels area.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________