Question Re: convert excel text format date to SAP ABAP format date in Technology Q&A

convert excel text format date to SAP ABAP format date

Former Member — Tue, 10 Sep 2013 10:02:41 GMT

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

Re: convert excel text format date to SAP ABAP format date

Former Member — Tue, 10 Sep 2013 10:05:09 GMT

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.

Re: convert excel text format date to SAP ABAP format date

Former Member — Tue, 10 Sep 2013 10:05:58 GMT

make sure in your are using data type as DATS.

.. Chandra..

Re: convert excel text format date to SAP ABAP format date

Former Member — Tue, 10 Sep 2013 12:32:11 GMT

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

Re: convert excel text format date to SAP ABAP format date

Former Member — Tue, 10 Sep 2013 14:03:37 GMT

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.

Re: convert excel text format date to SAP ABAP format date

Former Member — Wed, 11 Sep 2013 04:58:17 GMT

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?

Re: convert excel text format date to SAP ABAP format date

Former Member — Wed, 11 Sep 2013 07:15:18 GMT

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

Re: convert excel text format date to SAP ABAP format date

Former Member — Wed, 11 Sep 2013 08:10:36 GMT

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.

Re: convert excel text format date to SAP ABAP format date

Former Member — Wed, 11 Sep 2013 11:07:10 GMT

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