Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite

I supplied the following code as an answer to this question on SO, but I was wondering if there was a better way to write out all those array loops. (i.e. perhaps using a Collection/Dictionary?) It seems clunky/cumbersome as-is.

EDIT: I did do some cleanup (see link above and code below). Still curious if there's a better way.

Function ContainedInMonth(OriginalStartDate As String, _
    OriginalEndDate As String) As Boolean

    Dim MonthSet As Variant
    Dim AryCounter As Integer, ISOOffset As Integer
    Dim StartYear As Integer, EndYear As Integer
    Dim StartWeek As Integer, EndWeek As Integer
    Dim StartDay As Integer, EndDay As Integer
    Dim FormattedStartDate As Date, FormattedEndDate As Date

    ' This section may (will) vary, depending on your data. 
    ' I'm assuming "YYYY-WW" is passed...
    ' Also, error/formatting checking for these values is needed
    ' and wil differ depending on that format.
    StartYear = Val(Left(OriginalStartDate, 4))
    StartWeek = Val(Right(OriginalStartDate, 2))
    EndYear = Val(Left(OriginalEndDate, 4))
    EndWeek = Val(Right(OriginalEndDate, 2))


    If StartYear <> EndYear Or StartWeek > EndWeek Then
        ContainedInMonth = False
    ElseIf StartWeek = EndWeek Then
        ContainedInMonth = True
    Else

        ' Using the calculation from wikipedia. Honestly, I'm not sure that
        ' I understand this bit, but it seemed to work for my test cases.
        ISOOffset = Weekday(CDate("1/4/" & StartYear), vbMonday) + 3 
        StartDay = (StartWeek * 7) - ISOOffset ' Adding 0 for start of week
        EndDay = (EndWeek * 7) + 6 - ISOOffset ' Adding 6 for end of week

        ' Set the starting day for each month, depending on leap year.
        If StartYear Mod 4 = 0 Then
            MonthSet = Array(0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335)
        Else
            MonthSet = Array(0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334)
        End If

        FormattedStartDate = 0:FormattedEndDate = 0

        For AryCounter = 11 To 0 Step -1
            If StartDay > MonthSet(AryCounter) And FormattedStartDate = 0 Then
                ' Using MM/DD/YYYY format - this may be different for you
                FormattedStartDate = CDate(AryCounter + 1 & _
                    "/" & StartDay - MonthSet(AryCounter) & "/" & StartYear)  
            End If

            If EndDay > MonthSet(AryCounter) And FormattedEndDate = 0 Then
                FormattedEndDate = CDate(AryCounter + 1 & _
                    "/" & EndDay - MonthSet(AryCounter) & "/" & EndYear)
            End If
        Next AryCounter


        ContainedInMonth = IIf(Month(FormattedStartDate) = Month(FormattedEndDate), True, False)
    End If

End Function

I had considered something like: 

If StartYear Mod 4 = 0 Then
    TempArray = LeapSet
Else
    TempArray = NonLeapSet
End If

'Now do loops

But I'm thinking(/wondering/hoping) there may be an even more simplified way to do this.

Of course, being that this is on CR, I'm happy to accept any other ideas/criticisms you have to offer. I am always trying to improve myself!

ANOTHER EDIT:

I never got any feedback on this, but in case anyone ever comes across this, I've posted the final result as an answer...

share|improve this question

1 Answer

up vote 2 down vote accepted

This is the final result of what was done with this code, though the code pertaining to the root question remained unchanged.

Function ContainsWhatMonths(OriginalStartDate As String, _
    OriginalEndDate As String) As Variant

    Dim MonthSet As Variant
    Dim AryCounter As Integer, ISOOffset As Integer
    Dim StartYear As Integer, EndYear As Integer
    Dim StartWeek As Integer, EndWeek As Integer
    Dim StartDay As Integer, EndDay As Integer
    Dim StartWeekStartDate As Date, StartWeekEndDate As Date
    Dim EndWeekStartDate As Date, EndWeekEndDate As Date
    Dim FormattedStartDate As Date, FormattedEndDate As Date
    Dim TotalMonths As Integer, OutputMonths As String

    StartYear = Val(Right(OriginalStartDate, 4))
    StartWeek = Val(Left(OriginalStartDate, 2))
    EndYear = Val(Right(OriginalEndDate, 4))
    EndWeek = Val(Left(OriginalEndDate, 2))

    If StartYear <= EndYear Then

        ' Using the calculation from wikipedia. Honestly, I'm not sure that
        ' I understand this bit, but it seemed to work for my test cases.
        ISOOffset = Weekday(CDate("1/4/" & StartYear), vbMonday) + 3
        StartDay = (StartWeek * 7) - ISOOffset ' Adding 0 for start of week
        EndDay = (EndWeek * 7) + 6 - ISOOffset ' Adding 6 for end of week

        ' Set the starting day for each month, depending on leap year.
        If StartYear Mod 4 = 0 Then
            MonthSet = Array(0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335)
        Else
            MonthSet = Array(0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334)
        End If

        For AryCounter = 11 To 0 Step -1
            If StartDay > MonthSet(AryCounter) Then
                ' Using MM/DD/YYYY format - this may be different for you
                StartWeekStartDate = CDate(AryCounter + 1 & _
                    "/" & StartDay - MonthSet(AryCounter) & "/" & StartYear)
                StartWeekEndDate = StartWeekStartDate + 6

                If Month(StartWeekStartDate) <> Month(StartWeekEndDate) Then
                    FormattedStartDate = DateSerial(StartYear, Month(StartWeekEndDate), 1)
                Else
                    FormattedStartDate = DateSerial(StartYear, Month(StartWeekEndDate) + 1, 1)
                End If

                Exit For
            End If
        Next AryCounter

        If EndYear Mod 4 = 0 Then
            MonthSet = Array(0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335)
        Else
            MonthSet = Array(0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334)
        End If

        For AryCounter = 11 To 0 Step -1
            If EndDay > MonthSet(AryCounter) Then
                EndWeekStartDate = CDate(AryCounter + 1 & _
                    "/" & EndDay - MonthSet(AryCounter) & "/" & EndYear)
                EndWeekEndDate = EndWeekStartDate + 6

                If Month(EndWeekStartDate) <> Month(EndWeekEndDate) Then
                    FormattedEndDate = CDate(Month(EndWeekEndDate) & "/1/" & EndYear) - 1
                Else
                    FormattedEndDate = CDate(Month(EndWeekEndDate) & "/1/" & EndYear)
                End If

                Exit For
            End If
        Next AryCounter

        ' Switch the commenting on these two lines to return the string
        'ContainsWhatMonths = Array()
        ContainsWhatMonths = vbNullString

        TotalMonths = (Year(FormattedEndDate) - Year(FormattedStartDate)) * 12 + _
            Month(FormattedEndDate) - Month(FormattedStartDate)

        If TotalMonths >= 0 Then

            For AryCounter = 0 To TotalMonths
                OutputMonths = OutputMonths & "," & _
                    Format(DateAdd("m", AryCounter, FormattedStartDate), "MM/YYYY")
            Next

            OutputMonths = Right(OutputMonths, Len(OutputMonths) - 1)

            ' Switch the commenting on these two lines to return the string
            'ContainsWhatMonths = Split(OutputMonths, ",")
            ContainsWhatMonths = OutputMonths
        End If

    End If

End Function
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.