Pivot Table

Hi Silvia,

Thank you for your question. Here is how you can show years, quarters and months in Fiscal Year order:

First, insert a new column in your original Pivot source data table and write the following formula:

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

This 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 4 or higher. If the month is less than 4, then zero will be added to the year, otherwise 1 will be added.

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 April.

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

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 1, so June is in fiscal quarter 1.

Thirdly, if you want the months to show as text, you could create a lookup table as shown below:

1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December

Then, add another column in the pivot source data and use a VLOOKUP to list the months:

=VLOOKUP(MONTH(A2),$N$7:$O$18,2,1) where Column A holds the dates and $N$7:$O$18 might be where the above lookup table is entered.

Once you have created all the above, you can bring the new fields into your Pivot Table and you will have the dates arranged in Fiscal order.

I hope this helps.

Kind regards
Marius Barnard
STL