Search code examples
dategoogle-sheetsgoogle-sheets-formulaweek-number

Regroup By Quarters in Google Sheets

I have two rows in Google Sheets :

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

enter image description here

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

enter image description here

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

Solution

  • you can get Quarters like:

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

    enter image description here

    for week numbers use:

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