List all months between two dates sequentially?

I'm trying to list out all the months between two dates across a single row in Excel. So far I have this:

SEQUENCE( ,"End date"-"Start Date" +1, "Start Date")

This does almost what I'd like, but lists every single DAY between the two dates. What simple fix should I make such that it would increment by month and display month/year in order? Thanks

2 Answers

Put the start date in A1 and the end date in A2. Pick a cell and enter:

=DATE(YEAR(A1),SEQUENCE(1,DATEDIF(A1,A2,"M")+1,MONTH(A1),1),1)

NOTE:

This is because this:

=SEQUENCE(1,DATEDIF(A1,A2,"M")+1,MONTH(A1),1)

produces a sequential list of month numbers 10,11,12,13,14,15,16. and month 13 is January of the following year.

I do believe that you are looking for this only:

• Formula in cell AN2:

  =IF(ROW()=2,0,IFERROR(INDEX(MONTH(EDATE(AL$1,ROW(AN$1:INDEX(AN:AN,DATEDIF(AL$1,AL$2,"m"))))),COUNT(AN$1:AN1)),""))

• Adjust cell references in the formula as needed.

• You may replace 0 with 1 (for first month i.e January).