I have a date format like this
1Q16 2Q16 3Q16 4Q16
Is there a way to get...
1Q16 + 4 output:
1Q16 - 3 output:
... using formulas only and not code?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.Sign up to join this community
mid() extract the quarter portion of the original date, here after referred to as Q. Similarly extract the year portion, here after referred to as Y. The amount to be added will be referred to as D.
To get the new quarter add D to Q and modulo by 4. This will be correct except it will equal 0 when it should be 4. Wrap this whole portion in an
if() to handle that case and we are done.
Moving on to the new year. Add Y to the
trunc() of (Q+D)/4
When Q+D is negative or a positive multiple of 4 we end up 1 to high. Add a check for that at the end.
Take these two values and concatenate them with a Q in-between
=if(mod(MID(E15,1,1)+F15,4)=0,4,mod(MID(E15,1,1)+F15,4)) &"Q"&mid(E15,3,4)+trunc((MID(E15,1,1)+F15)/4)+ if(or(mod(MID(E15,1,1)+F15,4)=0,MID(E15,1,1)+F15<0),-1,0)
Original date is in E15 and the number of quarters to be added to it in F15
This answer is not Y2K compliant, but that's on you and your 2 digit years, if you go to 4 digit you will need to change the
mid() for the year to be