Grouping Pivot Table Dates by Fiscal Year

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

pivotgroupyearqtr

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.

pivotgroupfiscal01

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.

pivotgroupfiscal02

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.

pivotgroupfiscal03

______________

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

______________

This entry was posted in Group and Total. Bookmark the permalink.

18 Responses to Grouping Pivot Table Dates by Fiscal Year

  1. NJ says:

    Thank you! All the other places were trying to do all these fancy things with fiscal quarters. I merely needed a date field showing fiscal year by date and this did the trick!

  2. JG says:

    What if your fiscal quarter dates are not the beginning of each month? For instance, my company’s Q2 ends 10-01-10 and Q2 ended 07-02-10. Do I have to do some manual grouping?

    • JG, you could create a lookup table on another sheet in the workbook, and enter the start date of each fiscal quarter. Then, in the pivot table source data, refer to that lookup table to calculate the fiscal quarter for each item.

  3. Jessica Swingle says:

    This fiscal year formula was so simple and awesome! All the others I found online were extremently complex.

  4. Aza says:

    I followed the instructions and my reference date in cell U2 is 31/01/2007, my fiscal period starts in April and the result shows 29/06/1905. This is the formula I used =YEAR(U2)+(MONTH(U2)>=4)
    Any ideas? (note uk date style)

  5. Aza says:

    Thanks Debra! x

  6. Ian says:

    Any idea about about how to group fiscal weeks?

  7. Amy says:

    I need help with a homework problem. I am supposed to create a company account for QuickBooks by sorting through notes left by the owner. There is a note saying Fiscal Year July-June. Which month should I select??? Is there something I am not realizing? The chapter I am in gives an example of one that chooses January. It is a critical thinking question and we are supposed to use our best judgement so I am thinking to use January but this class has so many tricky little things like this. Any thoughts would be appreciated.

  8. CM says:

    Yes! Thanks for this! All the other solutions out there either don’t work or are too complex. This totally did the trick!

  9. Ron says:

    Praise the LORD. These formulas/solutions worked very well and were easy to apply.
    I certainly appreciate your Help!

  10. Steve says:

    this works well when all dates are the same year but my data crosses multiple years and i’m having trouble figuring this one out… FY is April through March.

    • Steve says:

      I used this formula for a FY ending on March 31st every year. there must be an easier function that i have not learned for using “OR” instead of 3 nested IF statements…

      I’d like the result to be displaied as FYxxxx… still working on that one.

      =IF(MONTH(D2)=1,YEAR(D2)-1,IF(MONTH(D2)=2,YEAR(D2)-1,IF(MONTH(D2)=3,YEAR(D2)-1,YEAR(D2))))

  11. Jesse H says:

    Thank you so much for sharing this!
    I have a project spanning 13-years of data and was looking for a way to categorise (it all) into Australian Fiscal Years (which excel doesn’t easily support) and searched a number of websites. This by far is the easiest solution.
    Thank you again!

  12. Dennis says:

    Excellent tips. Thank you so much!

  13. Chris says:

    I required the format FYXX/XX …. which was easy enough to accomplish.

    The above formula would give me “2013” for April 2012… which is actually “FY12/13” resulted in me jigging the formula:
    =”FY”& (YEAR(D2)+(MONTH(D2)>=4)-2001) & “/” & (YEAR(D2)+(MONTH(D2)>3)-2000)

    It’s no longer a date, but the formula above didn’t result in a date either. It’s now a string, rather than a number, but the pivot doesn’t have an issue grouping this either.,.

    Likewise, I don’t work in quarters, but months instead… so I use the formula:
    =CHOOSE(MONTH(D2),10,11,12,1,2,3,4,5,6,7,8,9)

    But I’ve had to type over the pivot table to read Apr, May etc…

  14. Anonymous says:

    Well this entire approach assumes that the “raw data” is “assumeably” editable.

    My data source is generated through SQL automatically and adding another column to my raw data is simply not an option.

    How can I create a calculated field in the Pivot table to solve this problem?

    I also don’t want to recreate the dynamic offset-function-based raw datasource because my file is already 15mB with nearly 50K records since 2001.

    Any better ideas on how to solve this problem?

Leave a Reply

Your email address will not be published. Required fields are marked *