Date conversion from Excel to the SAP format

[Former Member]

Hi,

I have created a BDC program which uploads daat from Excel sheet.

There is a date field also, the format of which can be any as per the end user (it will be one of those allowed by SAP).

However, the file will be used for execution by another user who may have a date setting which is different than that from the one in Excel.

Eg. End User's date format: DD.MM.YYYY, SAP User's date format: MM/DD/YYYY

So, how do I convert the date format from excel to the format which will be accepted by the transaction during the recording?

Regards,

Dave

[Former Member]

Hi Dave,

As I understand your requirement, I think the best possible solution for you is to get the date format streamlined in the excel sheet. I don't think SAP can recognize in which format the date is in the excel on its own.

Say, you get the date format streamlined as DD.MM.YYYY, then the end users shall always have to enter the date in this particular format.

Then in the program you can use the FM "KCD_EXCEL_DATE_CONVERT" to convert the date in SAP internal format which is YYYYMMDD.

In the FM, you can specify 4 date formats: TMJ / MTJ / JMT / JTM, where, J = Year, M = Month & T = Date.

So, you can discuss with your end users as to which format they prefer. They have no other option to choose from.

Then you can easily convert this date in the internal format to the format of the SAP user by using the WRITE statement.

e.g.. WRITE w_internaldt TO w_sapdt, where, w_sapdate is of type char10.

Hope this helps.

Regards

[Former Member]

Hi

Use the FM CONVERT_DATE_TO_INTERN_FORMAT to convert it into internal sap format . This will work I have tested in se37

IMPORTING

DATE 24032011

DTYPE DATS

For this you need to upload the data first into internal table .

loop at itab into wa.

call func CONVERT_DATE_TO_INTERN_FORMAT

endloop.

Thanks

Hariharan

[Former Member]

Hi Dave,

I have faced the same issue while uploading the date to the bdc.

if ur date format is DD.MM.YYYY in the excel file, first of all you need to change this to dats format ie YYYYMMDD ie character8 format and pass to the bdc, it will automatically convert to sap format

ie you need to convert splitting at .

data lv_date type char10,

lv_dd type char02,

lv_mm type char02,

lv_yyyy type char04,

v_date type char08.

lv_date = '30.12.2011'.

split lv_date at '.' into lv_dd lv_mm lv_yyyy.

if strlen(lv_dd) lt 2.

concatenate '0' lv_dd into lv_dd.

endif.

if strlen(lv_mm) lt 2.

concatenate '0' lv_mm into lv_mm.

endif.

concatenate lv_yyyy lv_mm lv_dd into v_date.

"Please use the variable V_DATE to populate to ur bdc"

Edited by: Rahul Babukuttan on Aug 11, 2011 5:35 PM

[former_member198094]

Hi,

I have also used the fm recommended by Hariharan.

If your issue is not being fixed, please update.

regards

[former_member198094]

Hi,

I have also used the fm recommended by Hariharan.

If your issue is not being fixed, please update.

regards

sadiq

[Former Member]

Thanks guys....resolved the issue by using the FM KCD_EXCEL_DATE_CONVERT