I wrote an article years ago explaining how to use a related table to handle financial years in Excel Pivot Tables. You can read the article here. If you only want the months in financial year order you can just add an extra column to your table.

In the formatted table below, we can add a new column to include the financial year month number and month name. This example uses the Australian financial year that starts in July.

The formula in cell N2 will be.

=TEXT(MONTH([@Date])+IF(MONTH([@Date])>6,-6,6),"00-")&TEXT([@Date],"mmm")

If you don’t use a formatted table, the formula would be.

=TEXT(MONTH(A2)+IF(MONTH(A2)>6,-6,6),"00-")&TEXT(A2,"mmm")

The resulting entry is shown below.

The TEXT function converts numbers and dates into formatted text. The format used is between the quotation marks. The & symbol joins the two TEXT results together to create a number and month.

The formula

MONTH(A2)+IF(MONTH(A2)>6,-6,6)

Converts a date into a financial year month number.

We need to use two digits for the number as it will be treated as text in the pivot table and sorted like text. In a text sort 1, 10, 11 and 12 would all be placed together, hence we need the leading zero to get the sort order working correctly.