0

I have a date format like this

1Q16
2Q16
3Q16
4Q16

Is there a way to get...

input: 1Q16 + 4 output: 1Q17 and...

input: 1Q16 - 3 output: 2Q15

... using formulas only and not code?

  • Are you starting from an actual date? eg: 1/1/2016 Or just your text entered as 1Q16? The VALUE() function along with some arithmetic may be the answer there. Would depend on your assumptions. – bjbk Apr 3 '18 at 1:29
3

Using 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 mid(3,6)

MID

TRUNC

MOD

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.