How to decrement quarterly for this format: yyyy-Qx (2022-Q1, 2022-Q2, 2022-Q3, etc.)

Question

[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. https://docs.google.com/spreadsheets/d/19woGGRDa-pMPAHMmP3tVMNTYfO56xwgmFx9PCg6czy0/edit#gid=0

You'll notice that, cell C4 is obtaining the quarterly date by referencing from A2, which is a dropdown list.

Now, I want D4 to reference C4 as well, however, I want it decremented by 1 quarter. So if C4 is "2022-Q4", D4 would be "2022-Q3".

[Research]
I know that you can do something similar if you used this formula: EDATE(C4,-1), which would decrement by 1 month. And I checked this website that explains about incrementing by quarter. https://infoinspired.com/google-docs/spreadsheet/extract-quarter-from-a-date-in-google-sheets-formula-options/ However, the challenge is that it's not in this format: 2022-Q1, which is a must.

Answer 1

Paste this to cell D4:

=LEFT(C4,4) + IF(OR(MOD(RIGHT(C4)-1,4)=0, (RIGHT(C4, 1)-1)<0),-1,0) & "-Q"&IF(MOD(RIGHT(C4)-1,4)=0,4,MOD(RIGHT(C4)-1,4))

To fill up E4 and F4, highlight D4 and drag the corner of cell D4 to E4 and F4.

Output:

References:

• LEFT
• RIGHT
• MOD
• IF