1

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:

source table example

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:

output table generated from the source table

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.

user2136000
  • 125
  • 6
  • 1
    If you know js,It would be better to write a script using apps script. This looks more like unpivot. – TheMaster Jun 20 '18 at 22:59
  • If that’s ultimately the right tool for the job, then yes...was just hoping to take it as far as I could with sheet functions. I’ll search that unpivot topic. – user2136000 Jun 20 '18 at 23:20
  • this should be doable. In a column which you can hide later, in the top cell (A2 in my case), place the earliest first day of the month possible in your data (or youcould calculate it with a min, I suppose). Immediately beneath place and then drag down as far as needed =if(month(A2)=12,date(year(A2)+1,1,1),date(year(A2),month(A2)+1,1)). I then adapted from https://productforums.google.com/forum/#!msg/docs/4FykKHzbXb8/tTt1_U_tDgAJ as follows (C3 and D3) were my start and end date: =query(A2:A,"select A where (A>=date '"&TEXT(C3,"yyyy-MM-dd")&"' and A <= date '"&TEXT(D3,"yyyy-MM-dd")&"')") – Jeremy Kahan Jun 21 '18 at 04:10
  • that gave me the exact list of dates I wanted. I'm thinking it might be extendable to do what you need. – Jeremy Kahan Jun 21 '18 at 04:12
  • you can get the format as you wish with =arrayformula(text(query(A2:A,"Select * where (A>=date '"&TEXT(C3,"yyyy-MM-dd")&"' and A <= date '"&TEXT(D3,"yyyy-MM-dd")&"')"),"MMM yyy")) – Jeremy Kahan Jun 21 '18 at 04:34
  • the number of rows you need for any given person is =index(query(A2:A,"Select count(A) where (A>=date '"&TEXT(C3,"yyyy-MM-dd")&"' and A <= date '"&TEXT(D3,"yyyy-MM-dd")&"')"),2) – Jeremy Kahan Jun 21 '18 at 04:47
  • and then it looks like once you have the counts, you can clinch with https://productforums.google.com/forum/#!topic/docs/_N4ITMMAajw – Jeremy Kahan Jun 21 '18 at 04:54
  • Thanks @JeremyKahan for going to the trouble and deconstructing the process. – user2136000 Jun 21 '18 at 15:55

1 Answers1

3

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'")

enter image description here

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

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • That's a gnarly query! Thanks for going to the trouble. May I ask why it is better to write a script using apps script, is it just going to be more readable/maintainable? – user2136000 Jun 21 '18 at 15:54
  • Wow, that is impressive. I got stuck when I could not put indirect inside an array formula. – Jeremy Kahan Jun 21 '18 at 20:58
  • @user2136000, the main reason for a script is the formula uses join+split construction which is possibly will lead to [the limit 50000 chars](https://stackoverflow.com/questions/22368544/new-google-spreadsheet-concatenate-limit-50000-characters) – Max Makhrov Jun 22 '18 at 05:57
  • 1
    It seemed to me, though I did not make it precise, that for that basic task of writing something n times you did not need to join and split or use rept. Rather, you could say something like =arrayformula(if (isblank(A1:A30),"this guy","this guy")), and then abstract it so the 30 is concatenated on and then you use indirect and "this guy" becomes a cell reference. But what I just wrote lets you create "this guy" 30 times. It is a little bizarre to use the if so trivially, but it works. – Jeremy Kahan Jun 22 '18 at 20:55
  • Thank you, @jeremy-kahan, nice idea! – Max Makhrov Jun 23 '18 at 06:25