# How to change fiscal year in Excel pivot table

CcarterjeffNew MemberJoinedNov 7, 2002Messages7Nov 8, 2002#1When I group information in a pivot table by date, and select quarters or years, it is of course based on a calendar yea

C

#### carterjeff

##### New MemberJoinedNov 7, 2002Messages7
• Nov 8, 2002
• #1When I group information in a pivot table by date, and select quarters or years, it is of course based on a calendar year. Can this be done by fiscal years?? using an addin or something, where I set the start month of the custom fiscal year.A

#### Alriemer

##### Board RegularJoinedAug 18, 2002Messages102
• Nov 8, 2002
• #2Carter,

Here is a function that figures out fiscal year and quarter for a date in a given month and year:

To get the quarter:
=INT((MONTH(Date)+IF(MONTH(Date)>FY_End,-(FY_End-2),(14-FY_End)))/3)
where Date is a reference to a cell containing the relevant date and FY_End is the month of the fiscal year end expressed as a whole number 1-12 (ie 9=September year end).

To get the fiscal year:
=DATE(YEAR(Date),MONTH(Date)+(12-FY_End),DAY(Date))

To get the quarter in the format "1Q02":
=TEXT(Quarter,"0Q")&TEXT(DATE(YEAR(Date_Plus_One),MONTH(Date_Plus_One)+(11-FY_End),DAY(Date_Plus_One)),"yy")
where Date_Plus_One the date of the month in question plus one month - ie, if Date was 6/30/02, Date_Plus_One should be 7/31/02.

Here is a quick method for getting the ending date of each month:
=DATE(YEAR(Date),MONTH(Date)+2,1)-1

Hope this helps
AlriemerC

#### carterjeff

##### New MemberJoinedNov 7, 2002Messages7
• Nov 9, 2002
• #3THese formulas are helpful, and I have used similar ones before. I was really looking to leave the date fields alone, and just alter the labels in the grouping function of a pivot table. I dont want oct-nov-dec months group as 4th quarter, but as 1st quarter.
thanks for your suggestions though, I may use them elsewhere.
jeffA

#### Alriemer

##### Board RegularJoinedAug 18, 2002Messages102
• Nov 9, 2002
• #4Jeff,

Let us know if you find a way. I know that stuff didn't speak straight to your problem but maybe it's a workaround.

AlriemerR

#### riclop

##### New MemberJoinedDec 16, 2008Messages10
• Sep 21, 2011
• #5Mr Excels Podcast here shows a work around. I suppose not what was wanted initially, but this does seem doable.

#### njdowell857

##### New MemberJoinedApr 19, 2012Messages1
• Apr 19, 2012
• #6With newer versions of excel you can use EDATE() for finding fiscal year.

For a year starting in July:

Code:=YEAR(EDATE(DATE(A1),6))
Where A1 is your date field.

As MrExcel does, place this in your main data table then pivot from the new column.