Search code examples
dategoogle-sheetstransposeflattengoogle-query-language

repeat n times an element between 2 dates and indicate the dates right next to it

How can I repeat an element between 2 dates and indicate the dates

Input values:

ID Begin End
ST00 May 15 2022 May 15 2022
TE01 May 23 2022 May 25 2022
TO01 May 16 2022 May 19 2022

Expected output:

ID Date
ST00 May 15 2022
TE01 May 23 2022
TE01 May 24 2022
TE01 May 25 2022
TO01 May 16 2022
TO01 May 17 2022
TO01 May 18 2022
TO01 May 19 2022

What I am doing right now :

in E2

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY( REPT(A2:A&"~", if(A2:A="",,C2:C-B2:B+1)),,9^9), "~"))))

in F2 (and dragging below)

=countif(E$2:E2,E2)

in G2

=arrayformula(iferror(vlookup(E2:E,A:B,2,0)+F2:F-1))

enter image description here

Is there a way to do whitout column F?

Solution

  • use:

    =ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(DAYS(C2:C, B2:B)>=SEQUENCE(1, 1000, ), 
 IF(A2:A="",,A2:A&"×"&TEXT(B2:B+SEQUENCE(1, 1000, ), "mmm dd e")), )), "×"), 
 "where Col2 is not null", ))

    enter image description here

    faster:

    =ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(DAYS(C2:C, B2:B)>=SEQUENCE(1, MAX(C2:C-B2:B+1), ), 
 IF(A2:A="",,A2:A&"×"&TEXT(B2:B+SEQUENCE(1, MAX(C2:C-B2:B+1), ), "mmm dd e")), )), "×"), 
 "where Col2 is not null", ))