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

How to change fiscal year in Excel pivot table

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.

    http://www.youtube.com/watch?v=sqO8knXFmtc&feature=channel_video_titleN

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.

Video liên quan