Convert Year and Week to a Data Range in Google Sheets-CodePudding

I have this row in Google Sheets with the year and Week yyyy-ww which starts at week "0".

     A           B             C        D       E
1  2022-00    2022-01        2022-02    ...    2023-00

How do I convert to a "Date Range" like this in Google Sheets?

          A                  B                   C              D      E
 1  Dec 26 - Jan 1    2 Jan - 8 Jan        9 Jan - 15 Jan      ...    ...

CodePudding user response：

I'm not sure if this is what you are looking for but this is a math formula that gets you your results. Paste it in A2 and it will spill over through all the columns.

=ARRAYFORMULA(IF(A1:1<>"",TEXT(DATE(LEFT(A1:1,4),1,1) (7*RIGHT(A1:1,2)-6),"mmm dd")&" - "&TEXT(DATE(LEFT(A1:1,4),1,1) (7*RIGHT(A1:1,2)),"mmm dd"),""))

You could then copy and paste the values over your original row. Assuming you want to convert your current format over to this new format permanently.

If you are looking for a way to literally format the cell, I do not believe that is possible from a yyyy-ww format.

UPDATE:

If curious, a shorter version using LAMBDA would look like:

=ARRAYFORMULA(LAMBDA(a,IF(a>0,TEXT(a,"mmm dd")&" - "&TEXT(a 6,"mmm dd"),""))(DATE(LEFT(A1:1,4),1,1) (7*RIGHT(A1:1,2)-6)))

CodePudding user response：

can you try this out:

=BYCOL(A1:G1,LAMBDA(ax,LAMBDA(s,w,LAMBDA(z,iz,IFS(iz=1,text(MAX(z)-6,"MMM D")&" - "&text(MAX(z),"MMM D"),iz=53,text(MIN(z),"MMM D")&" - "&text(MIN(z) 6,"MMM D"),(iz<>1) (iz<>53),text(MIN(z),"D MMM")&" - "&text(MAX(z),"D MMM")))(FILTER(s,w=--RIGHT(ax,2) 1,YEAR(s)=--LEFT(ax,4)),FILTER({w},w=--RIGHT(ax,2) 1,YEAR(s)=--LEFT(ax,4))))(SEQUENCE(DAYS("12/31/2025", "1/1/2020") 1,1,DATE(2020,1,1)),INDEX(WEEKNUM(SEQUENCE(DAYS("12/31/2025", "1/1/2020") 1,1,DATE(2020,1,1)))))))