Share via

How to adjust timeline and date grouping in PivotTable to a different calendar year

Anonymous
2023-08-21T19:32:26+00:00

Hello

My client goes on a different fiscal calendar year that the one by default in Excel. It goes from July 1, yyyy to June 30, yyyy

How can I get the PivotTable group field and the Timeline tool customized to that calendar year instead of from Jan 1, yyyy to Dec 31, yyyy?

So for example, when I specify Quarter 1 in a filter, it will give me all records with dates 7/1/yy to 9/30/yy instead of 1/1/yy to 3/31/yy

thanks very much for any assistance!

Craig

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2023-08-22T01:47:21+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.

    With Fiscal Date Table.

    Grouped by FYear, FQtr, FMonth.

    Fiscal start month: July.

    With PivotChart and Slicers.

    With cumulative FYear.

    https://www.mediafire.com/file_premium/kozvdod6pgw1os7/10_27_22.xlsx/file https://www.mediafire.com/file_premium/2xpo6k6sfpzccbj/10_27_22.pdf/file

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2023-08-21T20:16:01+00:00

    An easy way is to use a column of formulas like this, for date entries starting in cell A2:

    =YEAR(EDATE(A2,-6)) +1 & " Q" & INT((MONTH(EDATE(A2,-6))-1)/3)+1

    or this (depending on if their fiscal year is 2024 or 2023 right now)

    =YEAR(EDATE(A2,-6)) & " Q" & INT((MONTH(EDATE(A2,-6))-1)/3)+1

    You can also split those into two cells.....

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest