View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
NickHK NickHK is offline
external usenet poster
 
Posts: 4,391
Default Returning each month between 2 dates

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

"klysell" .(donotspam) wrote in message
...
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.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098