Given two dates, I’d like to loop through the months. If my start date is 1-Oct-2007 and my end date is 30-Sep-2008, then I’d like my loop counter to be
10,11,12,1,2,3,4,5,6,7,8,9
as it loops through the dates. It might look like this:
Sub LoopMonths()
For Month = dtStartDate To dtEndDate
x = CallFunction(Month)
Next Month
End Sub
For Month = dtStartDate To dtEndDate
x = CallFunction(Month)
Next Month
End Sub
Here’s how I do it now, but it doesn’t feel right.
Sub LoopMonths()
Dim i As Long
Dim dtStartDate As Date, dtEndDate As Date
Dim x As Variant
dtStartDate = #10/1/2007#: dtEndDate = #9/30/2008#
For i = dtStartDate To dtEndDate
If Day(i) = 1 Then
x = CallFunction(Month(i))
End If
Next i
End Sub
Dim i As Long
Dim dtStartDate As Date, dtEndDate As Date
Dim x As Variant
dtStartDate = #10/1/2007#: dtEndDate = #9/30/2008#
For i = dtStartDate To dtEndDate
If Day(i) = 1 Then
x = CallFunction(Month(i))
End If
Next i
End Sub
Where I’m using this, I know that my dates cover whole months. If that wasn’t the case, Day(i) wouldn’t work.
So what’s the right way?
I might use a Do loop and DateAdd, adding a month at a time until the end date is reached
Maybe something like this:
Dim StartMonth As Long
Dim i As Long, k As Long
StartMonth = 10
For i = 1 To 12
k = ((StartMonth + i – 1) – 1) Mod 12 + 1
Debug.Print i, k
Next i
End Sub
Not tested very well, and I quit before getting to the EndMonth part. After all, it’s Saturday night.
I love that Mod operator (and function). But it always takes me some trial and error to get it right.
The easiest way to represent the loopingh variable in a function is:
=mod(month(startdate)+12)
The easiest way to represent the loopingh variable in a function is:
=mod(month(startdate)+12,12)
Apologies for the two earlier comments. The easiest way to represent the looping variable in a function is:
=mod(month(startdate)+n,12)
Taking n from 0 to 11
Perhaps go to VBA Datediff function on this?
Something like
iStartMonth = Month(dtStartMonth)
For iMonth = iStartMonth to iStartMonth + Datediff(“m”,dtStartDate,dtEndDate)
Let VB do the math
Dim dtStart As Date, i as Long
dtStart = Now
For i = 0 To 11
Call myDate(DateSerial(Year(dtStart), Month(dtStart) + i, Day(dtStart)))
‘or
Call myMonth(Month(DateSerial(Year(dtStart), Month(dtStart) + i, Day(dtStart))))
Next
End Sub
I would use DateDiff and DateAdd.
Dim dtStartDate As Date, dtEndDate As Date
Dim lngNMonths As Long
Dim lngItem As Long
Dim strTemp As String
dtStartDate = #10/1/2007#
dtEndDate = #9/30/2008#
lngNMonths = DateDiff(“M”, dtStartDate, dtEndDate)
For lngItem = 0 To lngNMonths
strTemp = strTemp & Month(DateAdd(“M”, lngItem, dtStartDate)) & “,”
Next
Debug.Print Left(strTemp, Len(strTemp) – 1)
End Sub
Or…
Dim i As Long
Dim dtStartDate As Date, dtEndDate As Date
Dim x As Variant
Dim iMonth As Integer
dtStartDate = #10/15/2007#: dtEndDate = #9/30/2008#
iMonth = Month(dtStartDate)
Debug.Print iMonth
For i = dtStartDate To dtEndDate
If Day(i) = 1 And Month(i) iMonth Then
Debug.Print (Month(i))
End If
Next i
End Sub
whoops…just realised previous code was seriously flawed…how about this…
Dim i As Long
Dim dtStartDate As Date, dtEndDate As Date
Dim x As Variant
Dim iMonth As Integer
dtStartDate = #10/2/2006#: dtEndDate = #9/30/2009#
Debug.Print Month(dtStartDate)
For i = dtStartDate To dtEndDate
If Day(i) = 1 And i dtStartDate Then
Debug.Print (Month(i))
End If
Next i
End Sub
I just wrote my solution, refreshed the page, then saw Andy Post the same thing – he beat me to it:
Dim dtStart As Date
Dim dtEnd As Date
dtStart = #10/1/2007#
dtEnd = #9/30/2008#
Call LoopMonths(dtStart, dtEnd)
End Sub
Sub LoopMonths(p_dtStart As Date, p_dtEnd As Date)
Dim lMonth As Long
Dim lMonthDiff As Long
Dim lStartMonth As Long
Dim sResult As String
‘ Get number of months between start and end months
lMonthDiff = DateDiff(“m”, p_dtStart, p_dtEnd)
‘ Get start month
lStartMonth = Month(p_dtStart)
For lMonth = 0 To lMonthDiff
sResult = sResult & ” “ & Month(DateAdd(“m”, lMonth, p_dtStart))
Next lMonth
MsgBox sResult
End Sub
I second keepITcool’s approach. Why bother Modding it if
DateSerial(2008, 13, 31)
returns 31-Jan-2009?
Hmmm… Depends what you’re trying to do with the code: list every month included between dtStartDate and dtEndDate?
That’s got some complications: anything that uses DateAdd – either explicitly, or implied in a modulo division – runs into the bug that adding a month to the last day of February lands you on the 28th (or 29th) of March instead of the last day of March. Ditto for any month 30 days in length followed by one of 31 days. The workaround of hopping back one day from the first of the month is inelegant and needs a lot of coding.
Bluntly, I don’t think there’s any escape from the need to loop through each and every day between dtStartDate and dtEndDate. Not without a truly horrible perversion of the loop structure and rather kludgy ‘magic number’:
Dim i As Long
Dim dtStartDate As Date, dtEndDate As Date
Dim iMonth As Integer, iPrevious As Integer
dtStartDate = “5 Oct 2007”
dtEndDate = “21 Sept 2008”
iPrevious = VBA.Month(dtStartDate)
Debug.Print iPrevious
For i = dtStartDate To dtEndDate
iMonth = VBA.Month(i)
If iMonth iPrevious Then
iPrevious = VBA.Month(i)
Debug.Print iPrevious
i = i + 27 ‘skip unnecessary iterations
End If
Next i
End Sub
Annoyingly, the ‘not equals’ sign <> in the branching statement iMonth (not equals) iPrevious isn’t rendered by the VB formatter.
Makes handy use of DateSerial normalizing date parts that are out-of-range:
Dim intYear As Integer, intMonth As Integer, i As Integer
Dim dtStartDate As Date, dtEndDate As Date, dtLoopDate As Date
Dim x As Variant
dtStartDate = #10/1/2007#: dtEndDate = #9/30/2008#
intYear = Year(dtStartDate): intMonth = Month(dtStartDate)
dtLoopDate = dtStartDate: i = 0
Do
x = CallFunction(Month(dtLoopDate))
i = i + 1
dtLoopDate = DateSerial(intYear, intMonth + i, 1)
Loop While dtLoopDate <= dtEndDate
End Sub
Sub LoopMonths()
Dim i As Date
Dim dtStartDate As Date, dtEndDate As Date
Dim x As Variant
dtStartDate = #10/1/2007#: dtEndDate = #9/30/2008#
i = dtStartDate
Do
x = CallFunction(Month(i))
i = DateSerial(Year(i), Month(i) + 1, 1)
Loop While i <= dtEndDate
End Sub
My solution assumes that you will not be looping through more than 1 year
Dim dtStartDate as Date, dtEndDate as Date
Dim x as variant
Dim nMonth as Integer
dtStartDate =#10/1/2007#: dtEndDate = #9/30/2008#
nMonth = month(dtStartDate)
do while nMonth month(dtEndDate)
x=CallFunction(nMonth)
nMonth = ((nMonth -1) mod 12) +1
Loop
end sub
My solution assumes that you will not be looping through more than 1 year. Sorry for the repost, but apparently I must use != do indicate not equals when posting
Dim dtStartDate as Date, dtEndDate as Date
Dim x as variant
Dim nMonth as Integer
dtStartDate =#10/1/2007#: dtEndDate = #9/30/2008#
nMonth = month(dtStartDate)
do while nMonth != month(dtEndDate)
x=CallFunction(nMonth)
nMonth = ((nMonth -1) mod 12) +1
Loop
end sub
okay, last edit I promise. Change the while loop to a do..until loop and you’re money.
so, i lied. I really need to not try to do this so quickly. I made a mistake in my assignment to nMonth. In order the advance the sequence the second line of code inside the loop should be below.
Usually I’m the one on the soapbox about elegant code, but gosh… is it really worth the effort on this one? Here is my 2 minute “beat it with a rock until it goes away” solution.
Dim startdate As Date
Dim enddate As Date
Dim startmonth As Long
Dim numberofmonths As Long
Dim firstmonth As Long
Dim count As Long
Dim thatmonth As Long
startdate = Now
enddate = startdate + 730 ‘this example is two years from now, but any future date will do
numberofmonths = DateDiff(“m”, startdate, enddate)
firstmonth = DatePart(“m”, startdate)
thatmonth = firstmonth
For count = 1 To numberofmonths
Debug.Print thatmonth ‘Here is where your “do stuff” goes
thatmonth = thatmonth + 1
If thatmonth > 12 Then thatmonth = 1
Next
End Sub
It’s kinda strange that no one used a data structure appropriate to the problem. For some reason, people seem to have decided to limit themselves to existing loop control structures and functions and operators.
To really do this correctly, introduce a structured data type that includes the year and the month. Add functions to support transfers to/from “real dates” as well as a “next month” function. One could also go all out and use a class module to create an appropriate type.
Realizing that I could “embed” the new data structure in an existing data type, I decided to be somewhat lazy. So, my “data structure” consists of YYYYMM embedded in a Long data type.
I need two functions, one to map a date to YYYYMM and another to return the next month
YYYYMM = Year(aDate) * 100 + Month(aDate)
End Function
Function NextYYYYMM(YYYYMM As Long)
If YYYYMM Mod 100 = 12 Then NextYYYYMM = YYYYMM + 100 – 11 _
Else NextYYYYMM = YYYYMM + 1
End Function
Now, the code to implement Dick’s requirement is relatively trivial
Dim CurrYM As Long, LimYM As Long
CurrYM = YYYYMM(#10/1/2007#)
LimYM = YYYYMM(#9/30/2008#)
Do While CurrYM < = LimYM
Debug.Print CurrYM
CurrYM = NextYYYYMM(CurrYM)
Loop
End Sub
One can also use the DateSerial function to implement the above…kind of. Neither KeepItCool nor Jon pointed out a potential pitfall with DateSerial. If a date doesn’t exist in a particular month, it will pick a date in the *next* month. So, if the current date is 31 Jan 2008, then 1 month out will be a date in March! Nonetheless, I had thought of using it in this case as below.
‘It is the caller’s responsibility to ensure that _
the day-of-month is meaningful in the desired month!
MonthsDiff DateSerial(Year(aDate), Month(aDate) + NbrMths, Day(aDate))
End Function
Sub MonthLoop1()
Dim I As Date
I = #10/1/2007#
Do While I < = #9/30/2008#
Debug.Print I
I = MonthsDiff(I)
Loop
End Sub