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