Excel Place All Dates in a Range in Cells

Question

I don't know if this is the right place to ask this, but I saw others asking Excel Questions here.

How can I get Excel to put all the Months and Years between two date ranges.

Like for example my date range is March 2004 - December 2008, so Excel should return

March 2004
April 2004
May 2004
...
November 2008
December 2008

Each cell in a column should have all the Months/Years between two dates.

Thanks

score 3 · Accepted Answer · answered Feb 26 '12 at 23:44

In Windows Excel 2010 you can just type "March 2004" into a cell and drag down as far as needed and it will increment by the month. For me it changed the format to look like "Mar-2004," but you can then format the cells with a custom format of "mmmm yyyy" to get the exact format above. To do a custom format, just right-click the cells with the dates ... oh wait, can you right-click? So, get the Format Cells dialog, choose the Number tab, then Custom at the bottom of the list, and type the format into the "Type" box near the top.

Or did you want something more automated than this?

score 1 · Answer 2 · answered Feb 26 '12 at 23:58

Doug's solution is undoubtedly the easiest way of getting a column of months. However, if you want a VBA solution, there is one below. You do not say why you cannot code such a routine yourself. I decided it was easier to code the routine than ask what you did not know. Ask if you do not understand what this code is doing.

Option Explicit
Sub TestDataRange()

  Call OutputDateRange("Sheet1", 2, 2, DateValue("1 Mar 2004"), _
                                       DateValue("1 Apr 2008"))

End Sub
Sub OutputDateRange(ByVal WShtName As String, ByVal RowTop As Long, _
                    ByVal Col As Long, ByVal DateFirst As Date, _
                    ByVal DateLast As Date)

  Dim DateCrnt As Date

  DateCrnt = DateFirst

  With Sheets(WShtName)
    Do While True
      With .Cells(RowTop, Col)
        .Value = DateCrnt
        .NumberFormat = "mmmm yyyy"
      End With
      DateCrnt = DateAdd("m", 1, DateCrnt)
      If DateCrnt > DateLast Then
        Exit Do
      End If
      RowTop = RowTop + 1
    Loop
  End With

End Sub

I've only done VBA on a Mac once and it was just a test to see how easily code ported. A lot of stuff worked and some required fiddling. — Doug Glancy, Feb 27 '12 at 00:02
@Doug Glancy. A subtlety that was lost on me; I will remember it for the future. Thanks. — Tony Dallimore, Feb 27 '12 at 09:20

Excel Place All Dates in a Range in Cells

2 Answers2