CleanDate(ActiveDate)

CleanDate(ActiveDate)

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:

A B
ActiveDate =+CleanDate(ColA)
201201 2012-01
2012-1 2012-01
2012-01 2012-01

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
This entry was posted in Articles, ddGlobal, Tips and tagged , . Bookmark the permalink.



Leave a Reply