Regroup By Quarters in Google Sheets

Question

I have two rows in Google Sheets :

Row 3 YYYY-MM (Month) number Row 5 YYYY-WW (Week) number

I want to regroup them by quarter, I have put one "ideal solution" and one alternative solution.

Is there any way I can do this in Google sheets?

here's the example

https://docs.google.com/spreadsheets/d/1mrOrBDCz5goV6E-MRWZifWy2dET63qEMSPD2AUvcsw4/edit?usp=sharing

Answer 1

you can get Quarters like:

=INDEX(TEXT(B3:O3, "yyyy-\Q"&VLOOKUP(MONTH(B3:O3), {1,1;4,2;7,3;10,4}, 2)))

for week numbers use:

=INDEX(TEXT(C3:P3, "yyyy-"&TEXT(WEEKNUM(C3:P3, 1), "00")))