Question VBA date conversion to SAP formats

[eatraas]

Hi,

i've been trying to convert a date format like dd/mm/yy to a SAP used format like dd.mm.yyyy .

a simple string conversion like

 pp = day(datefield) & "." & month(datefield) & "." & format(year(datefield),"YYYY")

is not working, the year is converted wrong.

thus 17-07-62 should be converted to 17.7.1962 ( european date format )

Please help.

Thanks in advance.

Erwin

 

[ByteMyzer]

Hello, eatraas,

First, you can make it much simpler thus:

pp = Format(datefield, "dd.mm.yyyy")

Second, SAP's internal date format is actually YYYYMMDD. Some of the RFC Function Modules do take date inputs in an external format, such as DD.MM.YYYY, but in truth, the external date format to use depends on the Regional Settings of the User Account used to connect to SAP.

 

[jjones]

Hello, eatraas,

First, you can make it much simpler thus:

pp = Format(datefield, "dd.mm.yyyy")

Second, SAP's internal date format is actually YYYYMMDD. Some of the RFC Function Modules do take date inputs in an external format, such as DD.MM.YYYY, but in truth, the external date format to use depends on the Regional Settings of the User Account used to connect to SAP.

Hello
I know this is an old thread but it represents my problem in VBA code exactly. I have been trying to call a BAPI (BAPI_CATIMESHEETMGR_INSERT) from vba to insert some data into SAP. However, the date field in the function structure (setup using Set Functions = CreateObject("SAP.Functions") will not allow me to send a value in SAPs required format of . instead of / in the date. If i run the same BAPI in SAP via SE37 the date has to be dd.mm.yyyy but in VBA, if i convert NOW() to that format as a string and add it to the function structure which gets sent to SAP, the structure expects a date format. However, if i convert the date in VBA, it always sets it as dd/mm/yyyy but i need a date format of dd.mm.yyyy
i've tried reformatting as a string and that works but i need to pass a date to the structure but in the format of dd.mm.yyyy
i've read loads of articles and posts but just can't seem to get there.
Any help would be much appreciated!
Many thanks in advance

 

[Gasman]

Wouldn't https://community.sap.com/ be a more appropriate forum?
Unless we have any people here who have to export to SAP?

Google Search

No doubt you found this thread with a google like above, but a few links down was this?

Convert normal dates into SAP dates using VBA | SAPLSMW.com

When dealing with the underlying functionality of ABAP, there tends to be just one valid date format: YYYYMMDD. Slashes and dashes throw SAP's functions into conniptions and the many international formats only make things more confusing. Converting regular date formats into YYYYMMDD in ABAP is...

saplsmw.com

? sapdate(format(date,"mm/dd/yyyy"))
20240308

 

[Pat Hartman]

1. Now() = date AND time. Date() = just date. Always use the correct function for the purpose.
2. "doesn't work" is useless. Show us your code.

 

[jjones]

Hello Team
Thanks for your responses and apologies for not providing details. Hopefully it's something really obvious and apologies in advance if it is! I've never tried connecting to SAP and calling BAPIs from Excel before although i do write ABAP code but my VBA is non-existent.
Here is the code. The connection to SAP is not included as this is all OK. I can run another BAPI to check the logged on user and that works fine but when i try and run a BAPI to submit data to SAP, the date format is changing and I believe SAP is rejecting as a result.
Thanks very much

 

[arnelgp]

Public Sub SAPDate(ByRef strDate As String)
' strDate
'
' mm/dd/yyyy
'
' return
'
' yyyy.mm.dd
'
    Dim strTemp As Variant
    strTemp = Split(strDate)(0)
    strTemp = Split(strTemp, "/")
    strDate = strTemp(2) &  strTemp(0) &  strTemp(1)
End Sub

 

[jjones]

Thanks Arnelgp
Perhaps the problem is the # at the beginning and end of the date rather than the format (#11/03/2024#)? Do you know if VBA will pass the date to SAP with the hash at the beginning and end or is it just how it shows it in VBA?

 

[arnelgp]

on, googling SAP don't have the hash, and it is string in the Format "YYYYMMDD"

 

[cheekybuddha]

Do you know if VBA will pass the date to SAP with the hash at the beginning and end or is it just how it shows it in VBA?

It will not pass the hash - the hash shows you that you are dealing with a VBA Date datatype (as opposed to a string or a number)

 

[jjones]

OK thanks all - i will investigate more on the SAP side now i know VBA is all good.
Thanks very much