Excel-VBA transfers incorrect date values from SAS to Excel-sheet

Question

My VBA code transfers a SAS-table to Excel-sheet via ADODB.Connection and "SAS.LocalProvider.1". Two SAS-columns are date in the format of YYYY-MM-DD, like 2015-09-01 and 2015-09-30.

I am also using format for those columns in my VBA-code like Sheet2.Range(Range("C2"), Range("C2").End(xlDown)).NumberFormat = "YYYY-MM-DD". Then it is supposed to convert the whole column into the correct date format.

However, in the end, I get date values in the Excel-sheet like 1955-08-31 and 1955-09-29 instead of 2015-09-01 and 2015-09-30.

I checked the SAS-table and both columns are numeric and the format is date as YYDDMM10..

I wrote in the VBA code as YYMMDD and YY-MM-DD as it is shown in the SAS-format, but it didn't help. It still gave the same year as 1955 (or only 55).

In the code, you can see that I tried to change the format "before the table-transer". But also I tried it "after the table-transfer" as well, but I got exactly the same result.

Public Sub SASTransfer()
  Dim rTarget1 As Range: Set rTarget1 = Sheet2.Range("A2")
  Dim sSasTable1 As String: sSasTable1 = SASOutputPath & "\" & SASOutput1 & ".sas7bdat"

    Sheet2.Range(Range("C2"), Range("C2").End(xlDown)).NumberFormat = "YYYY-MM-DD" ' This should change the format of the whole C-column
    Sheet2.Range(Range("D2"), Range("D2").End(xlDown)).NumberFormat = "YYYY-MM-DD"

    Dim con1 As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    con1.Provider = "SAS.LocalProvider.1"
    con1.Open
    rs1.Open sSasTable1, con1, adOpenForwardOnly, adLockReadOnly, ADODB.adCmdTableDirect
    rTarget1.CopyFromRecordset rs1
    rs1.Close
    Set rs1 = Nothing
    con1.Close
    Set con1 = Nothing

End Sub

Answer 1

• SAS uses 1/1/1960 as its reference date for day 0.

• Excel uses 1/0/1900 (i.e. 12/31/1899) as its reference date for day 0.

The number of days between 1/1/1960 and 12/31/1899 is 21,916.

Since you're going from SAS to Excel, add 21916 to the SAS date.

data excel_export;
     set have;
     Excel_Date = SAS_Date + 21916;
     format Excel_Date mmddyy10.;
run;