User’s SAP date format might a problem while using Excel VBA application connected to SAP . If SAP date format is different then Excel default format we will not able to make mathematics operation with dates.

Correct Excel date format is YYYY-MM-DD, but other world regions could have different approach. If Excel format date is YYYY-MM-DD when we downloading SAP data with different date format this is DD.MM.YYYY then Excel will see date as text. In order to check what format is using in SAP, SU3 has to be use.

To use code that has been created by me it is important to turn on SAP id controls in SAP settings. Thanks to this we could use this code or eventually customize it for our needs.
In order to get format date Excel has to be connected to SAP. Please see this post: Connection to SAP HANA via Excel VBA.

Private Sub GetSAP_DateFormat()
' Getting user's SAP date format
' There is need to turn on SAP id controls in SAP settings (manual will be add when get access to SAP)

Dim IdDatySAP, TempDateFormat, FormatDatySAP 
Dim LicznikWierszyLX02 as Long

On Error GoTo ErrorHapenned
LicznikWierszyLX02  = 1

' ---------------------------------------------------------------------------
' GETTING  DATE FORMAT FROM SU3
  
Session.FindById("wnd[0]").Maximize
Session.FindById("wnd[0]/tbar[0]/okcd").Text = "su3"
Session.FindById("wnd[0]").SendVKey 0
Session.FindById("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA").Select
TempDateFormat = Session.FindById("wnd[0]/usr/tabsTABSTRIP1/tabpDEFA/ssubMAINAREA:SAPLSUID_MAINTENANCE:1105/cmbSUID_ST_NODE_DEFAULTS-DATFM").Text

' ---------------------------------------------------------------------------
' BACK TO SESSION MANAGER

Session.FindById("wnd[0]/tbar[0]/okcd").Text = "/n"
Session.FindById("wnd[0]").SendVKey 0

' ---------------------------------------------------------------------------
' DATE FORMAT (don't have access to SAP now - possible changes required)

' Ustalanie formatu daty
FormatDatySAP = Left(TempDateFormat, InStr(1, TempDateFormat, " ") - 1)
IdDatySAP = FormatDatySAP 

' Get format date - control id elimination
'If FormatDatySAP > 10 Then
'    FormatDatySAP = Mid(TempDateFormat, 3, InStr(3, TempDateFormat, " ") - 3)
'End If

' ---------------------------------------------------------------------------
' SAP DATA PROCESSING IN EXCEL

' Here should be pasted code to import and paste SAP data

' ---------------------------------------------------------------------------
' DATE FORMAT EXCHANGE

' Don't have access to SAP, but I think code to change date to proper one should like as follow
' Code is using SAP controls id.
' Instead to use control ID, date format can be used
' Date has been pasted to cell earlier

Select Case IdDatySAP
    Case 1
    .Sheets(SheetName).Cells(LicznikWierszyLX02, 13) = Right(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 4) & "-" & _
    Mid(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 4, 2) & "-" & _
    Left(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 2)
    
    Case 2
    .Sheets(SheetName).Cells(LicznikWierszyLX02, 13) = Right(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 4) & "-" & _
    Left(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 2) & "-" & _
    Mid(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 4, 2)
    
    Case 3
    .Sheets(SheetName).Cells(LicznikWierszyLX02, 13) = Right(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 4) & "-" & _
    Left(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 2) & "-" & _
    Mid(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), 4, 2)
    
    Case 4
    .Sheets(SheetName).Cells(LicznikWierszyLX02, 13) = Replace(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), ".", "-", 1)

    Case 5
    .Sheets(SheetName).Cells(LicznikWierszyLX02, 13) = Replace(.Sheets(SheetName).Cells(LicznikWierszyLX02, 13), "/", "-", 1)
     
    Case 6
    ' correct format
End Select

Exit Sub

' ----------------------------------------------------------
' POSSIBLE ERRORS HANDLING

ErrorHapenned:
ErrOpis = Err.Description
ErrStatus = True
ErlLine = Erl

End Sub