[What I want to do]
By using a formula, I want to be able to increment the quarterly based date when referencing a quarterly date that's like this: 2022-Q1, 2022-Q2, 2022-Q3, 2022-Q4. However, from my understanding, there isn't a dedicated formula that can compute this conveniently.

To make this more clear, I've created a sample sheet.

References:

• LEFT
• RIGHT
• MOD
• IF

CodePudding user response：

Try in D4 and drag to the right

=regexextract(C4,"\d ") if(REGEXEXTRACT(C4,"Q(\d)")-1,0,-1)&"-Q"&REGEXEXTRACT(C4,"Q(\d)")-1 if(REGEXEXTRACT(C4,"Q(\d)")-1,0,4)

CodePudding user response：

A few shorter versions:

=if(right(C4)="1", left(C4,4)-1, left(C4, 4)) & "-Q" & mod(right(C4) 2,4) 1

=left(C4,4) floor(right(C4)/5-0.3) & "-Q" & mod(right(C4) 2,4) 1