on 09-10-2013 11:02 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
make sure in your are using data type as DATS.
.. Chandra..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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?
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.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.