ddBuildDate(ActiveDate,Increment,Optional=OutFormat)

ddBuildDate is a function within ddGlobal which forms part of the ddDate suite of macros.  ddBuildDate uses the text field “YYYY-MM” or the numeric format “YYYYMM” to do date calculations on.  So assume you have a number “200506” (June 2005) in cell “A1” and you would like to add one month to that, then in theory it is simple +=a1+1 and that will produce you the correct date, being 200507.  The problem comes in if you would like to jump between 200512 and 200601, now you require more complex formulas to move to the new date.  Alternatively, you could use “+ddBuildData(200512,1)” which will increment the date, 200512 with 1.  Now obviously you could change the increment (1 in the above scenario) to any number you want, or you could even make it a negative number if you would like to go back in time.

I appreciate that you could use Excel’s build in dates, however, what frustrates me with those dates is that it is very dependent on the user’s regional settings and thus limits you on how it will display on a different computer.

Originally ddBuildDate was used as headings in a report or dashboard allowing the user to select a start date (drop down boxes / sliders etc) and also how many months the user will like to see in each column (1 month, 2, months, 6 months etc).  Using something like +SumIF() to populate the data, one could have a “display” sheet which from a formatting perspective is unchanged irrespective what data or time intervals the user will look at.

Obviously, ddBuildDate works very well with the other functions within ddGlobal including LargeDate and BuildCurDate.

See the attached example spreadsheet on how the formula ddBuildDate works in its most basic form:

Increment Start date t1 t2 t3 t4
1 200510 200511 200512 200601 200602
3 200510 200601 200604 200607 200610
-1 200503 200502 200501 200412 200411
-3 200503 200412 200409 200406 200403
1 2005-10 2005-11 2005-12 2006-01 2006-02
4 2005-10 2006-02 2006-06 2006-10 2007-02
-1 2005-03 2005-02 2005-01 2004-12 2004-11
-4 2005-03 2004-11 2004-07 2004-03 2003-11

The formula for T1 to T4 is basically reading

=+ddbuilddate(cell-to-the-left-of-me,Increment-from-first-column)

ddBuildDate has an optional formatting function which allows you to convert the output from either YYYYMM or from YYYY-MM to the other format.  This is done by entering the following formulae:

=+ddBuildDate(DateToChange,Increment,“YYYYMM”)
=+ddBuildDate(DateToChange,Increment,“YYYY-MM”)

Please note that the optional formatting use in the above scenario is case sensitive.  If the macro is in doubt what want, it will give you YYYYMM.

Function DDBuildDate(ActiveDate As String, Increment As Integer, Optional OutFormat As String)
'This function adds / subtracts a date from the activedate, very useful if you need to obtain months in a row or column
'Denis added the optional OutFormat on 2011-05 due to the requirement to occasionally use YYYYMM instead of YYYY-MM

    Dim DDMonth As Integer
    Dim DDYear As Integer
    Dim TmpMonth As String
    
    If OutFormat = "" Then
        If Len(ActiveDate) = 6 Then OutFormat = "YYYYMM"
        If Len(ActiveDate) = 7 Then OutFormat = "YYYY-MM"
    End If
    DDYear = Val(Left(ActiveDate, 4))
    DDMonth = Val(Right(ActiveDate, 2))
    
    Do Until Increment < 12 And Increment > -12
        If Increment < -1 Then
            DDYear = DDYear - 1
            Increment = Increment + 12
        Else
            DDYear = DDYear + 1
            Increment = Increment - 12
        End If
    Loop
    
    DDMonth = DDMonth + Increment
    If Increment < 0 Then
        If DDMonth < 1 Then
            DDYear = DDYear - 1
            DDMonth = DDMonth + 12
        End If
    Else
        If DDMonth > 12 Then
            DDYear = DDYear + 1
            DDMonth = DDMonth - 12
        End If
    End If
    
    TmpMonth = DDMonth
    If DDMonth < 10 Then TmpMonth = "0" & TmpMonth
    
    If OutFormat = "YYYY-MM" Then
        DDBuildDate = DDYear & "-" & TmpMonth
    Else
        DDBuildDate = Val(DDYear & TmpMonth)
    End If
End Function

The macro has been tested on various versions of Excel dating back to 2000. It may not be the most efficient piece of code, but at least it does what it says on the tin.

This entry was posted in Articles, ddGlobal, Tips and tagged , . Bookmark the permalink.



Leave a Reply