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:
The formula for T1 to T4 is basically reading
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:
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.