When uploading Excel file with a date, it looks like '41851'', not '7/31/2014'

Question

When uploading an Excel file in Web Dynpro for ABAP with date, the date looks like 41851 instead of 7/31/2014.

How can I solve this problem?

score 1 · Answer 1 · answered Oct 22 '15 at 09:08

1

Excel stores Dates as numbers. To display that number as Date as you know it, format the cell as Date cell.

answered Oct 22 '15 at 09:08

Verzweifler

930
6
16

In Excel already has a date in the format of the cells. But when I upload to the web dynpro, they are displayed as I wrote in the question. When i change the date in format of cells entirely into the text ,it displayed as the 03.20.2015 (for example), then it works. But I need not to change the date in excel. And find a way to replace those numbers to date. – Nikita Stelmaschenko Oct 22 '15 at 09:17
So you have found a way to display the correct date in excel. What is your problem from here on? If the date is displayed incorrectly in Web Dynpro, you will have to change the formatting there. – Verzweifler Oct 22 '15 at 09:23
if it would be possible, I would have done it. And it is necessary to convert that number to date. I tried through the formula and the function,but has not found. I read about these numbers in Excel, and that's what i found: "All dates are stored as integers representing the number of days since January 1, 1900, which is stored as number 1, to December 31, 9999 stored as 2958465." and: "42005 is 1-Jan-2015 (because it is 42,005 days after January 1, 1900)" – Nikita Stelmaschenko Oct 22 '15 at 09:41
Please make sure that the actual value of the cell reads "7/31/2014", since using formatting may obfuscate the real value. In this case, try a formula as suggested by @ojf. – Verzweifler Oct 22 '15 at 10:45

score 1 · Answer 2 · answered Oct 22 '15 at 10:36

1

Use this formula =TEXT(41851,"YYYY-mm-dd") and the date should upload correctly (you can change the format string to whatever you need).

answered Oct 22 '15 at 10:36

score 0 · Accepted Answer · answered Oct 22 '15 at 12:44

I found solution:

data: lv_data type sy-datum,
      lv_startdate  type sy-datum.

lv_startdate = '19000101'. "starting date(excel parameter)

lv_data = lv_startdate + 41851(the date from excel that we need to convert to normal date) - 2.

write lv_data.

this code works, thx all for help.

When uploading Excel file with a date, it looks like '41851'', not '7/31/2014'

3 Answers3