Fiscal Year Quarter Formula

[steve400243]

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


=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","")))))

[Admin]

=[@Year]+(--MONTH([@[Comp Date]])>=6)&LOOKUP(MONTH([@[Comp Date]]),{1,"-Q3";3,"-Q4";6,"-Q1";9,"-Q2";12,"-Q3"})

[steve400243]

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","")))))

[steve400243]

=[@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.