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
|