# Looping Through Months

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

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

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?

Posted in Uncategorized

## 22 thoughts on “Looping Through Months”

1. dbb says:

I might use a Do loop and DateAdd, adding a month at a time until the end date is reached

2. Maybe something like this:

Sub GenerateMonths()
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.

3. The easiest way to represent the loopingh variable in a function is:

=mod(month(startdate)+12)

4. The easiest way to represent the loopingh variable in a function is:

=mod(month(startdate)+12,12)

5. 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

6. Neal O says:

Perhaps go to VBA Datediff function on this?

Something like

iStartMonth = Month(dtStartMonth)
For iMonth = iStartMonth to iStartMonth + Datediff(“m”,dtStartDate,dtEndDate)

7. keepITcool says:

Let VB do the math

Sub LoopMonths()
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
8. Andy Pope says:

I would use DateDiff and DateAdd.

Sub LoopMonths()
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 &amp; Month(DateAdd(“M”, lngItem, dtStartDate)) &amp; “,”
Next
Debug.Print Left(strTemp, Len(strTemp) – 1)

End Sub
9. Dan Randles says:

Or…

Sub LoopMonths()

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
10. Dan Randles says:

Sub LoopMonths()

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
11. I just wrote my solution, refreshed the page, then saw Andy Post the same thing – he beat me to it:

Sub testMonths()
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 &amp; ” “ &amp; Month(DateAdd(“m”, lMonth, p_dtStart))
Next lMonth

MsgBox sResult
End Sub

12. I second keepITcool’s approach. Why bother Modding it if

DateSerial(2008, 13, 31)

returns 31-Jan-2009?

13. 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’:

Sub LoopMonths()

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
14. Annoyingly, the ‘not equals’ sign <&GT in the branching statement iMonth (not equals) iPrevious isn’t rendered by the VB formatter.

15. Chris Akers says:

Makes handy use of DateSerial normalizing date parts that are out-of-range:

Sub LoopMonths()

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 &lt;= dtEndDate

End Sub
16. 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

17. Ryan says:

My solution assumes that you will not be looping through more than 1 year

Sub loopMonths

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

18. Ryan says:

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

Sub loopMonths
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

19. Ryan says:

okay, last edit I promise. Change the while loop to a do..until loop and you’re money.

20. Ryan says:

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.

nMonth = ((nMonth mod 12) +1)
21. Dan Maxey says:

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.

Sub loopmonths()

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 &gt; 12 Then thatmonth = 1
Next

End Sub

22. 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

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

Sub MonthLoop()
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.

Function MonthsDiff(aDate As Date, Optional NbrMths As Integer = 1)
‘It is the caller’s responsibility to ensure that _
the day-of-month is meaningful in the desired month!