Pivot week numbers from Google Sheets and Google Analytics

Question

Google Sheets, on using WEEKNUM or ISOWEEKNUM displays first year weeks without zero, like 20201, 20202 and so on.

Google Analytics, its metric ga:YearWeek delivers week numbers of first year weeks with zero, like 202001, 202002 and so on.

I have in one column week numbers given by ISOWEEKNUM, and in another table some data based on GA's ga:YearWeek. How can i pivot them smartly? Currently both week numbers don't match.

---

PS: i've tried to add a zero to week numbers, don't having it on the beginning, with

=IF((REGEXMATCH("A2";"^[0-9]{1}$"));(CONCAT("0";A2));"A2")

It works in one cell, but i can't pull the formula to the bottom - everywhere remains A2 because of "". And without "", with formula like

=IF((REGEXMATCH(A2;"^[0-9]{1}$"));(CONCAT("0";A2));"A2")

i run into an issue

Error
Function REGEXMATCH parameter 1 expects text values. But '28' is a number and cannot be coerced to a text.

PPS: i'm on german Google Sheets - there should be ; instead of ,.

Answer 1

A simpler solution to concatenating is using &, and using the TEXT function to add the leading zero like this:

=TEXT(WEEKNUM(A1),"00")

So, the full formula to transform 01/01/2020 into 202001 would be:

=YEAR(A1)&TEXT(WEEKNUM(A1),"00")

Answer 2

try like this:

=ARRAYFORMULA(IF(A1:A="",,
 YEAR(A1:A)&IF(WEEKNUM(A1:A)<10, "0"&WEEKNUM(A1:A), WEEKNUM(A1:A))))

Answer 3

You could use this formula:

=CONCATENATE(YEAR(A1),(IF(WEEKNUM(A1)<10, CONCATENATE("0",WEEKNUM(A1)), WEEKNUM(A1))))

It takes the year from the date specified and adds the week: if the number of the week is less than 10, then it adds a 0, else, it leaves it as it is.

Also, you can drag the formula to the bottom so you can use it in all the cells in the column you want.