Generate array of dates with 1 month interval in Google Sheets

3
votes

I'm trying to generate an array of dates in Google Sheets, where I know the first date, the last one and I want the other dates to be at 1-month interval, with the date always being the last day of the month.

StartDate (C3) = 2019-03-31
EndDate (I3) = 2019-06-30

Expected outcome:

2019-03-31
2019-04-30
2019-05-31
2019-06-30

This is the code I have tried

=ArrayFormula(ADD(C3,row(INDIRECT("C1:C"&eomonth(I3,0)-C3))))

But it's returning this

2020-04-01
2020-04-02
2020-04-03
2020-04-04
2020-04-05
2020-04-06
2020-04-07
2020-04-08
2020-04-09
2020-04-10
2020-04-11
2020-04-12
2020-04-13
2020-04-14
2020-04-15
2020-04-16
2020-04-17
2020-04-18
2020-04-19
2
dategoogle-sheetsgoogle-sheets-formulaarray-formulas

2 Answers

4
votes

Please try:

=ARRAYFORMULA(EOMONTH(C3,ROW(INDIRECT("a1:a"&DATEDIF(C3,I3,"M")+2))-1))

  • =DATEDIF(C3,I3,"M")+2 = the number of monthes between 2 dates includind these dates.
  • =ARRAYFORMULA(ROW(INDIRECT("a1:a3"))) = basic counter
2
votes
=ARRAYFORMULA(TEXT(UNIQUE(EOMONTH(ROW(INDIRECT(
 DATEVALUE(C3)&":"&DATEVALUE(I3))), 0)), "yyyy-MM-dd"))

0