VBA - Period Id arithmetics (e.g. adding months to "201502" which stands for 'Feb 2016')

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' mPeriod

' Period related auxiliary operations/methods.

'

'

'

'

'

' A period ID is always defined as the year concatenated with a 2-digit#

' representing the month of the given date.

' For instance, for March 2011, the equivalent period id would be 201103.

'

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

' 1. getPeriodID

Public Function getPeriodID(Optional dt As Variant) As Long

    ' Validate

    If IsMissing(dt) Then

        dt = Now()

    ElseIf IsDate(dt) Then

        If dt = CDate(0) Then dt = Now()

    ElseIf IsNumeric(dt) Then

        If dt >= 190001 And dt <= 209912 Then

            getPeriodID = dt

            Exit Function

        Else

            dt = Now()

        End If

    Else

        dt = Now()

    End If

   

    ' Return

    getPeriodID = ((Year(dt) * 100) + Month(dt))

End Function

' 2. getPeriodDate

' Will return the first day of the month for the given period id

' CHANGE THIS TO USE ARITHMETICS INSTEAD OF COSTLY STRING FUNCTIONS

Public Function getPeriodDate(PeriodID As Long) As Date

    ' Return

    getPeriodDate = CDate(Right(CStr(PeriodID), 2) & "/1/" & Left(CStr(PeriodID), 4))

End Function

' 3. getPeriodIDDiff

' If no end date is given, this function will assume the end to be the current

' period id according to the system's date.

Public Function getPeriodIDDiff( _

    PeriodIDStart As Long _

    , PeriodIDEnd As Long _

) As Integer

    ' Validate

    'If IsMissing(PeriodIDStart) Then PeriodIDStart = 0

    'If IsMissing(PeriodIDEnd) Then PeriodIDEnd = getPeriodID()

   

    ' Return

    getPeriodIDDiff = CInt(DateDiff("m", getPeriodDate(PeriodIDStart), getPeriodDate(PeriodIDEnd)))

   

End Function

' 4. getPeriodAdd

Public Function getPeriodAdd(PeriodID As Long, Addition As Integer) As Long

    ' Return

    getPeriodAdd = getPeriodID(DateAdd("m", Addition, getPeriodDate(PeriodID)))

   

End Function