CleanDate and ddCleanDate are both functions within ddGlobal and forms part of the ddDate suite of macros.
ddCleandate is kept just for compatibility with older spreadsheets but works the same as CleanDate. CleanDate will take various formats of numbers and convert them into a “clean-date” being represented as a text field YYYY-MM. See the following example:
Future enhancements of this function should include the ability to convert Excel Dates into the text format YYYY-MM but it is easy enough to just write a formula for that to achieve the same result
+=CleanDate(Year(Excel_Date) & “-” & month(Excel_Date))
Herewith the code
Function CleanDate(ActiveDate As Variant) 'The function is used to add an additional ZERO in the case of the date being written as: '2001-1 instead of 2001-01. 'It also verifies whether or not the number is 200109 (instead of the norm "2001-09" 'and adds a "-" char if it is found to be true. 'Last update by Denis Dell on 10/10/2001 If IsNumeric(ActiveDate) Then 'the value send through is actually a number 200109 etc Select Case ActiveDate Case Is >= 190000 'i.e. the date is coming through as 200109 CleanDate = Trim(Str(ActiveDate)) CleanDate = Left(CleanDate, 4) & "-" & Right(CleanDate, 2) End Select Else 'the value is a text such as is 2001-09 Select Case Len(ActiveDate) Case Is = 7 CleanDate = ActiveDate Case Is = 6 CleanDate = Left(ActiveDate, 5) & "0" & Right(ActiveDate, 1) End Select End If End Function