CODEWITHSUNDEEP
vbaexcelisodate-formatting
matandked
matandked

Reputation: 1573

VBA - check if some time period is contained entirely in some month

I have information about some time periods given as start and end weeks (defined as ISO weeks, so they may start not only on Monday).

Regarding ISO definition, a month may contain 4 or 5 weeks description on wikip. I want to check if some time periods are entirely contained in some months and after it execute next commands.

How can I do this in Excel VBA? Are there any special functions which can help me to achieve the mentioned check?

Upvotes: 1

Views: 1798

Answers (1)

Gaffi
Gaffi

Reputation: 4367

Does this help?

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

This is tested code (which works as a worksheet function or through VBA) based on some of my own assumptions. (Need your data to test for sure...) If you do have a specific format example, I will change this code to match.

This assumes you will be passing the right variables to the right position in the function. There is not a proper check for start/end date order, though you should not get any errors at least.

Also, there may be more efficient ways to do this rather than looping through those arrays, but this works.

What this does, simply, is calculate dates of the first day of the given starting week and the last day of the given ending week. If both of these dates are in the same month, then the function returns true.

With minor tweaking, this can we made to report the starting dates of both the first and last week, in case you are worried about the start of the week more than the full week.

Test cases used:

Start   End     Result  
2012-01 2012-05 FALSE  
2012-01 2012-04 TRUE  
2012-05 2012-07 FALSE  
2012-25 2012-26 TRUE  
2012-52 2012-01 FALSE  
2012-28 2012-25 FALSE

EDIT:

Following your examples provided, here is an updated function. This will work as-is as a VBA function returning an array (variant) of the formatted dates/months that you are looking for. To convert this to a worksheet function, just some minor tweaking to return a string (already created in the function - see comments) is needed.

I am running with the assumption that your examples were wrong (see my test cases), but this can be modified to work if it is me who is wrong.

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

Test cases:

"18-2010", "20-2010"  'Null
"17-2010", "20-2010"  'Null
"17-2010", "21-2010"  '05/2010
"18-2010", "25-2010"  '06/2010
"17-2010", "25-2010"  '05/2010,06/2010
"19-2010", "26-2010"  '06/2010

Upvotes: 1

Related Questions