ConvertDate(ActiveDate, Optional IsFromSas)

ConvertDate is a function within ddGlobal and forms part of the ddDate suite of macros.  It is used to convert various formats of dates into the text format YYYY-MM.  Excel dates counts the days from January 01, 1900 up words.  SAS for instance counts the days from 1961 etc.  So a date like September 1999 in an excel date will be 36,410, whereas the same date in SAS will be 14,494.  Using SAS dates and Excel dates in the same spreadsheet is bound to cause some errors somewhere along the line.

The reason why the ddDates suite of macros was created was exactly to avoid the above confusion.  I settled on the text field YYYY-MM or the numerical value YYYYMM.  This works nicely as a standard and a standard that is also very useful when you save file names.  People saving files as YY-MM or MM-YY makes it extremely difficult to sort the files in explorer, but that is another topic.

Any event, below is an example of you to use the formula

Type of date Unformated c d
SASDate (unformated) 14,494 1999-09 =+convertdate(B1,TRUE)
ExcelDate (unformated) 36,410 1999-09 =+convertdate(B2)

where column c is the output from the code used in column d.

Function ConvertDate(ActiveDate As Variant, Optional IsFromSas = False)
'The function is used to convert an Excel Date(Numeric) into a "YYYY-MM" Date:
'i.e 36410 = 1999-09
'If IsFromSAS is TRUE then it converts a SAS Date(numeric)
'i.e 14494 = 1999-09
'Last update by Judah&Denis on 28/11/2001

    Dim Y, m As Integer
    If IsFromSas Then ActiveDate = ActiveDate + 21916
    Y = Year(ActiveDate)
    m = Month(ActiveDate)
    ConvertDate = CleanDate(Y & "-" & m)
End Function
This entry was posted in Articles, ddGlobal, Tips and tagged , . Bookmark the permalink.

Leave a Reply