0

What I'm trying to do is to join few arrays of dates.

For start, I have start date and amount of months I want to generate. Which I did by using =ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2) + SEQUENCE(C2, 1, 0, 1), 1)) formula, where A2 is start date and C2 is amount of months to generate.

Formula I cannot come up to is to do same for all existing rows and join all generated arrays of dates in a single column.

Also, I'd like to copy some columns next to every date to identify from which row it is. I have done this by formula =TRANSPOSE(SPLIT(JOIN(",", ARRAYFORMULA(REPT(SPLIT(D2,",")&",", C2))), ",")) which I found in google.

Also, here is what I have reached so far and you can test your versions too.

Sample

1
  • 1
    @OlegSkrypyuk Why not post your comment as an answer? It makes it easier for future users to see that there is an answer to the question, and they don't have to trawl through the comments to find it.
    – Tedinoz
    Mar 6, 2020 at 9:32

1 Answer 1

0

It just combines these formulas, so if you need to add some more data - have to do it manualy

={ TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(D2&",", C2))), ",")), 
ARRAYFORMULA(DATE(YEAR(A2), MONTH(A2) + SEQUENCE(C2, 1, 0, 1), 1));
TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(D3&",", C3))), ",")),
ARRAYFORMULA(DATE(YEAR(A3), MONTH(A3) + SEQUENCE(C3, 1, 0, 1), 1));
TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(D4&",", C4))), ",")),
ARRAYFORMULA(DATE(YEAR(A4), MONTH(A4) + SEQUENCE(C4, 1, 0, 1), 1));
TRANSPOSE(SPLIT(JOIN(",",ARRAYFORMULA(REPT(D5&",", C5))), ",")),
ARRAYFORMULA(DATE(YEAR(A5), MONTH(A5) + SEQUENCE(C5, 1, 0, 1), 1)) }
1
  • Hi, that's what I did to show what result I want to get (and what I have reached so far). But I want it to automatically generate for each rows so I don't have to add that for each record. Also, it might be reached by another formula, if it'll do what I need, it will be acceptable.
    – nikodz
    Mar 6, 2020 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.