Calc Months in Year for a several year span

[cariengon]

I was hoping to solve this using a query, but a note on the my original thread said to try coding...

We are using DateDiff to find the number of months. But we need to see how many months are in each year that a contract is valid - Here's an example of the results we're looking for:

A contract starts on 6/1/03 and ends on 6/30/05. How many months in '03, how many months in '04, how many months in '05.

Our result needs to be:
2003 - 7 months
2004 - 12 months
2005 - 6 months

Any ideas on how to do this?

 

[MichaelRed]

Not necessarily the way I would think up to get these values, as I do not really see how you can use them (particularly in an SQL environment), but perhaps it will give you some other thoughts. I included the "test" routine as a way to illustrate how to return multiple values from a function. Again, not that it is suitable for use in SQL as shown here, but I have noted that many practicioners (of Ms. A. / VBA) are not well versed in these 'techniques', so thought some might find it interesting,

Public Function basMonthsByYear(dtStrt As Date, dtEnd As Date) As Integer()

    'Return an Array of Years and Months in the Years from two dates _
     6/1/03 and ends on 6/30/05 should return: _
     2003 - 7 months _
     2004 - 12 months _
     2005 - 6 months

    Dim YrsMnths() As Integer
    Dim dtTemp As Date
    Dim Idx As Integer
    Dim MyYr As Integer
    Dim EndMnth As Integer
    Dim StrtYr As Integer
    Dim StrtMnth As Integer
    Dim EndYr As Integer

    'Some BASIC err checks and work avoidance code
    If (dtStrt = dtEnd) Then
        'No problemo, so no answero
        GoTo NoRtn
    End If

    If (dtStrt > dtEnd) Then
        'Screwed up, swap for user convience
        dtTemp = dtStrt
        dtStrt = dtEnd
        dtEnd = dtTemp
    End If

    'Initalize Locals
    StrtYr = Year(dtStrt)
    EndYr = Year(dtEnd)
    MyYr = Year(dtStrt)
    StrtMnth = Month(dtStrt)

    ReDim Preserve YrsMnths(EndYr - StrtYr, 1)

    While Idx <= UBound(YrsMnths, 1)
        'Tell me the year, the old old year ...
        MyYr = StrtYr + Idx
        YrsMnths(Idx, 0) = MyYr     'Year is easily known for all cases

        Select Case MyYr

            Case Is = StrtYr    'Starting Yr
                StrtMnth = Month(dtStrt)
                If (StrtYr = EndYr) Then
                    EndMnth = Month(dtEnd)
                 Else
                    EndMnth = 12
                End If

            Case Is = EndYr     'Ending Year (if not same as Starting Year)
                StrtMnth = 1
                EndMnth = Month(dtEnd)

            Case Else
                StrtMnth = 1
                EndMnth = 12

        End Select

        YrsMnths(Idx, 1) = EndMnth - StrtMnth + 1

        Idx = Idx + 1
    Wend

    basMonthsByYear = YrsMnths

NoRtn:
    GoTo NormExit


NormExit:
    Exit Function

End Function

Public Function tstMonthsByYear()

    Dim MyMnthYr As Variant
    Dim Idx As Integer

    MyMnthYr = basMonthsByYear(#6/1/2003#, #6/30/2005#)

    While Idx <= UBound(MyMnthYr, 1)

        Debug.Print MyMnthYr(Idx, 0) & " - "; MyMnthYr(Idx, 1)
    
        Idx = Idx + 1
    Wend



End Function


MichaelRed
mlred@verizon.net

 

[PHV]

Hi MichaelRed, very nice post.
Just a note, what happens if Option Base 1 ?

 

[MichaelRed]

then you either change the array bounds and indicies or get wrong answere and / or errors.




MichaelRed
mlred@verizon.net

 

[AnanthaP]

if month(enddate) < month(begindate) then
result = (year(enddate) - 1) - year(begindate) + month(endate) + 12 - month(begindate)
else
result = (year(enddate) - 0) - year(begindate) + month(endate) - month(begindate)
end if

I checked if for
Dec'04 to Jan'05 and
Nov 04 to Dec 04.

I think of it as a whole number with a "months digit" and an "year digit". Render / borrow of your basic school maths.

Possible? Not sure of exact VbScript syntax.

With an immediate-if you can also put it in a cell value and the syntax might be:

if(month(enddate) < month(begindate), (year(enddate) - 1) - year(begindate) + month(endate) + 12 - month(begindate),
(year(enddate) - 0) - year(begindate) + month(endate) - month(begindate))

End