Search code examples
dategoogle-sheetsgoogle-sheets-formulauniquearray-formulas

Generate array of dates with 1 month interval in Google Sheets

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
Solution 
  • =ARRAYFORMULA(TEXT(UNIQUE(EOMONTH(ROW(INDIRECT(
 DATEVALUE(C3)&":"&DATEVALUE(I3))), 0)), "yyyy-MM-dd"))

    0