Is there a Google Sheets formula or query to get an array of months within a date range?

Question

I have a start date and an end date, and DATEDIF easily gets me the number of months within there. I'd like to write out each month within that range, e.g. for a date range:

Mar 1, 2016 - Jul 31, 2018

...I want to iterate over the number of months in that range and return a row for each month in the range, starting from the start date:

Mar 2016
Apr 2016
May 2016
(the rest of 'em)
Apr 2018
May 2018
Jun 2018

This may be a better illustration of what I'm after. This table shows my source, a sheet of unique subscriptions, each having an ID, a rate, and start and end date:

The table I want to generate automatically would iterate through each unique subscription ID row, and then give me a row for each months' subscription payment, across the start-end dates for that subscription ID:

This second table I just want to make dynamic and dependent on what's in that first table, to save time.

I understand I have to just put in the time and figure out how to do it like everyone else has, but if you know if this is even possible just using Google Sheets' standard functions and queries, I'd be filled with a renewed sense of hope.

Thanks in advance for any patient pointers.

Answer 1

I was curious if it is possible with a formula.

Here's the result: =query({TRANSPOSE(SPLIT(TEXTJOIN("",1,TRANSPOSE(ARRAYFORMULA(TEXT(ROW(INDIRECT("a1:a"&MAX(FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))))*(TRANSPOSE(FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))>=ROW(INDIRECT("a1:a"&MAX(FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))))),"0"","";;")))),",")), ARRAYFORMULA(TEXT(EOMONTH( VLOOKUP(TRANSPOSE(SPLIT(JOIN("",REPT(ROW(INDIRECT("A1:A"&COUNTA(A2:A)))&",",FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))),",")),FILTER({ROW(A2:A)-ROW(A2)+1,D2:D},A2:A<>""),2,), TRANSPOSE(SPLIT(TEXTJOIN("",1,TRANSPOSE(TEXT(ROW(INDIRECT("a1:a"&MAX(FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))))*(TRANSPOSE(FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))>=ROW(INDIRECT("a1:a"&MAX(FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))))),"0"","";;"))),","))-1 ),"MMM YYY")), ArrayFormula(VLOOKUP(TRANSPOSE(SPLIT(JOIN("",REPT(ROW(INDIRECT("A1:A"&COUNTA(A2:A)))&",",FILTER(DATEDIF(D2:D,E2:E,"M"),A2:A<>""))),",")),FILTER({ROW(A2:A)-ROW(A2)+1,A2:C},A2:A<>""),{2,4},))},"select Col1, Col3, Col2, Col4 label Col1 'Payment_Id', Col3 'Subscription_Id', Col2 'PaymentMonth', Col4 'PaymentAmount' format Col4 '$0.00'")

If you know js, It would be better to write a script using apps script.