Returning each month between 2 dates

[Guest]

Hi,

I have a "start date" in $B$12 and an "end date" in $C$12. Starting in
$B$16, I need to have each month between these two dates populated. It would
be nice if there were duplicates of each month in this column. For example,
if $B$12 was April 1, 2006 and $C$12 was March 31, 2007, a procedure would
place "April-06" in $B$16 and $B$17. "May-06" would be placed in $B$18 and
$B$19, "June-06" would be placed in $B$20 and $B$21, and so on until the last
month of "March-07" would be placed in the final two cells in column B. I
have 53 rows formatted to accept a list of months, so I would like the
remainding rows after the last entered month ("March-07" in the example), to
be hidden.

Is this an easy procedure to code? As usual, I would be very appreciable if
someone would be able to help me on this problem.

Thanks in advance!
Kent.

 

[NickHK]

Kent,
For a worksheet function, try this. It is an array formula, so select the
output cell, enter the function and arguments as required and press
SHIFT+CONTROL+ENTER, not just ENTER.
If you do not want to duplicate the month list, set the Duplicate argument
to False.
You should add checks for valid dates and error trapping:

Public Function GetMonths(StartDate As Date, EndDate As Date, Optional
Duplicate As Boolean = True) As Variant
Dim MonthCount As Long
Dim TempDates() As Date
Dim i As Long
Dim NewDate As Date

MonthCount = DateDiff("m", StartDate, EndDate)

If Duplicate = True Then
ReDim TempDates(0 To MonthCount * 2 + 1)
Else
ReDim TempDates(0 To MonthCount)
End If

For i = 0 To MonthCount
NewDate = DateAdd("m", i, StartDate)
If Duplicate = True Then
TempDates(i * 2) = NewDate
TempDates(i * 2 + 1) = NewDate
Else
TempDates(i) = NewDate
End If
Next

If Application.Caller.Columns.Count > 1 Then
GetMonths = TempDates
Else
GetMonths = Application.WorksheetFunction.Transpose(TempDates)
End If

End Function

NickHK

 

[Arvi Laanemets]

Hi

Into cell B16 enter the formula
=IF(DATE(YEAR($B$12),MONTH($B$12)+INT((ROW()-16)/2),1)>DATE(YEAR($C$12),MONTH($C$12),1),"",DATE(YEAR($B$12),MONTH($B$12)+INT((ROW()-16)/2),1))
, and format as Custom "mmm-yy" or "mmmm-yy".
Copy B16 down for as much as you planned.

 

[Guest]

Hi Kent -

See comment in code below for labeling options.

Sub klysellDoubleMonth()
With Range("B16:B68")
.NumberFormat = "@"
.HorizontalAlignment = xlCenter
End With
startDate = Range("B12").Value
endDate = Range("C12").Value
nextDate = startDate: i = 0

Do
nextDate = DateSerial(Year(startDate), Month(startDate) + i, Day(startDate))
'Next statement: change True to False for non-abbreviated month name
dateLabel = MonthName(Month(nextDate), True) & _
"-" & Right(Year(nextDate), 2)
Range("B16:B17").Offset(i * 2, 0) = dateLabel
i = i + 1
Loop Until Month(nextDate) = Month(endDate) And _
Year(nextDate) = Year(endDate)

Range(Range("B16").End(xlDown)(2, 1), _
Range("B68")).EntireRow.Hidden = True
End Sub

 

[Guest]

Wow! You guys are the Excel gods of the planet. Thanks so much!