convert excel text format date to SAP ABAP format date

[Former Member]

Hi All,

I am uploading an excel file(.xlsx) to ALV in webdynpro. I am able to upload data, however the date field is read as text rather than date type.

Hence I am getting 41500 instead of  8/14/2013.

If you type  8/14/2013 in excel cell and right click and format cell and change the format to text type you will see that  8/14/2013 changes to 41500.

How can I convert back 41500 to 8/14/2013 in ABAP?

Do we have any FM available?

Help will be highly appreciated.

Regards,

Prashant

[Former Member]

Hi,

Could you please try below function modules to convert date from excel to sap format and let me know.

KCD_EXCEL_DATE_CONVERT

CONVERT_DATE_TO_INTERN_FORMAT

Best Regards,

Abirami

[Former Member]

make sure in your are using data type as DATS.

.. Chandra..

[former_member186077]

Hi Prashant,

Kindly save the field as text and provide the date in the required format, then excel does not change the field to internal format.

Thanks and Regards,

Sriranjani Chimakurthy.

[Former Member]

Hi Sriranjani,

Thanks for reply.

The below thread solves my problem, however it calculates date difference with January 01, 2008.

Is there any optimal solution?

https://scn.sap.com/thread/1860122

Regards,

Prashant

[former_member186077]

Hi Prashant,

Does your excel has got a formula linked to it?

The ideal one would be to change the date column to text.

Thanks and Regards,

Sriranjani Chimakurthy.

[Former Member]

No, there is no formula linked. I am exporting the excel sheet using in-built export to excel functionality and importing back by changing some financial values.

The method worksheet->get_cell is getting all data as CSTRING. As in excel 2007, Text format of date returns numeric value, hence it is auto-typecasting it to text.

Refer the thread in my previous reply for details.

Is there any way to convert the numeric date back to sap date format?

[former_member186077]

Hi Prashant,

I did little browsing on how Excel stores the date format, i found the below link has got useful information.

http://answers.oreilly.com/topic/1694-how-excel-stores-date-and-time-values/

It states that excel by default considers the date January 1, 1900 as Day 1 and does the conversion for internal format.

Since you were able to solve the issue with this thread https://scn.sap.com/thread/1860122  you can go ahead with that logic. However if the excel is manually generated and if someone changes the property to 1904 as mentioned then you might have an issue.

Thanks and Regards,

Sriranjani Chimakurthy.

[Former Member]

Hi Sriranjani,

Thanks a lot for your effort.

I can understand the potential risk of changing property to 1904 date system. I know this is not the best solution for the issue. That's why I am looking for an optimal solution.

Please try to find one with me.

Regards,

Prashant