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

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

  3. Perhaps go to VBA Datediff function on this?

    Something like

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

  4. 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
  5. 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 & Month(DateAdd(“M”, lngItem, dtStartDate)) & “,”
        Next
        Debug.Print Left(strTemp, Len(strTemp) – 1)
       
    End Sub
  6. 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
  7. whoops…just realised previous code was seriously flawed…how about this…

    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
  8. 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 & ” “ & Month(DateAdd(“m”, lMonth, p_dtStart))
      Next lMonth
     
      MsgBox sResult
    End Sub

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

  11. 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
  12. 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

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

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

  15. 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)
  16. 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

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

    Function YYYYMM(aDate As Date)
        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

    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!
       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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *