Printable View
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","")))))
=[@Year]+(--MONTH([@[Comp Date]])>=6)&LOOKUP(MONTH([@[Comp Date]]),{1,"-Q3";3,"-Q4";6,"-Q1";9,"-Q2";12,"-Q3"})
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:
R3Quote:
=TEXT([@[Comp Date]],"yyyy")
Code:
S3Quote:
=CHOOSE(MONTH([@[Comp Date]]),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug", "Sep","Oct","Nov","Dec")
Code:
Quote:
=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","")))))
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.Quote:
Originally Posted by Admin