Results 1 to 4 of 4

Thread: Fiscal Year Quarter Formula

  1. #1
    Junior Member
    Join Date
    Jun 2017
    Posts
    4
    Rep Power
    0

    Fiscal Year Quarter Formula

    Hello, I use this formula to group data on a pivot table by our Fiscal year, could it be shorter?

    Code:
    =IF(MONTH([@[Comp Date]])=1,[@Year]&"-Q3",IF(OR(MONTH([@[Comp Date]])=3,MONTH([@[Comp Date]])=4,MONTH([@[Comp Date]])=5),[@Year]&"-Q4",IF(OR(MONTH([@[Comp Date]])=6,MONTH([@[Comp Date]])=7,MONTH([@[Comp Date]])=8),[@Year]+1&"-Q1",IF(OR(MONTH([@[Comp Date]])=9,MONTH([@[Comp Date]])=10,MONTH([@[Comp Date]])=11),[@Year]+1&"-Q2",IF(MONTH([@[Comp Date]])=12,[@Year]+1&"-Q3","")))))

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    =[@Year]+(--MONTH([@[Comp Date]])>=6)&LOOKUP(MONTH([@[Comp Date]]),{1,"-Q3";3,"-Q4";6,"-Q1";9,"-Q2";12,"-Q3"})
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Jun 2017
    Posts
    4
    Rep Power
    0
    Thanks for looking at this Admin - My apologies for not explaining what I was looking for better - Maybe this will help. I appreciate your time.

    starting in B3 is the completed date, then in

    Q3 i have
    Code:
    =TEXT([@[Comp Date]],"yyyy")
    R3
    Code:
    =CHOOSE(MONTH([@[Comp Date]]),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug", "Sep","Oct","Nov","Dec")
    S3
    Code:
    =IF(MONTH([@[Comp Date]])=1,[@Year]&"-Q3",IF(MONTH([@[Comp Date]])=3,MONTH([@[Comp Date]])=4,MONTH([@[Comp Date]])=5),[@Year]&"-Q4",IF(MONTH([@[Comp Date]])=6,MONTH([@[Comp Date]])=7,MONTH([@[Comp Date]])=8),[@Year]+1&"-Q1",IF(OR(MONTH([@[Comp Date]])=9,MONTH([@[Comp Date]])=10,MONTH([@[Comp Date]])=11),[@Year]+1&"-Q2",IF(MONTH([@[Comp Date]])=12,[@Year]+1&"-Q3","")))))

  4. #4
    Junior Member
    Join Date
    Jun 2017
    Posts
    4
    Rep Power
    0
    Quote Originally Posted by Admin View Post
    =[@Year]+(--MONTH([@[Comp Date]])>=6)&LOOKUP(MONTH([@[Comp Date]]),{1,"-Q3";3,"-Q4";6,"-Q1";9,"-Q2";12,"-Q3"})
    This does work as needed, I was just looking for a way to shorten the formula, or not use an IF formula. I am pretty new to the advanced formulas and VBA, and everything I have learned has been from the help forum's. Thank you for your time, and the formula.

Similar Threads

  1. Test Copy Do you know when Easter is this year?
    By Rick Rothstein in forum Test Area
    Replies: 6
    Last Post: 08-01-2021, 08:23 PM
  2. Happy New Year
    By mahmoud-lee in forum Excel Help
    Replies: 7
    Last Post: 01-02-2014, 10:08 PM
  3. Happy New Year
    By mahmoud-lee in forum Greetings and Inception
    Replies: 7
    Last Post: 01-02-2014, 10:08 PM
  4. Replies: 1
    Last Post: 11-22-2013, 12:56 AM
  5. Find Quarter Month From Date In Excel
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 15
    Last Post: 04-19-2012, 02:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •