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
