Skip to Content
avatar image
Former Member

Excel upload date format is like "41851"

Hello all! I have a problem with excel date in my program. When i upload date, like date format in excel it upload in my web dynpro like 41851 instead 7/31/2014. How can i solve this problem without changing date in excel?

regards,Nikita

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Oct 22, 2015 at 12:49 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      There is a problem with this approach. Excel thinks that there was the day 02/29/1900, but it was not. The solution would be:

         DATA: l_excel_date        TYPE string,
               l_excel_day_string  TYPE string,
               l_dummy_text        TYPE string,
               l_excel_days        TYPE i.

         l_excel_date = i_excel_date.

         REPLACE ALL OCCURRENCES OF '.' IN l_excel_date WITH ','.
         SPLIT l_excel_date AT ',' INTO l_excel_day_string l_dummy_text.

         CLEAR:  l_dummy_text.

         l_excel_days = l_excel_day_string.
         e_date = c_excel_start_date. "19000101

         IF l_excel_days > 0 AND l_excel_days < 60.
           l_excel_days = l_excel_days - 1.
         ELSEIF l_excel_days <= 0.
           l_excel_days = 0.
         ELSEIF l_excel_days = 60 OR l_excel_days = 61.
           l_excel_days = 0.
           e_date = '19000301'.
         ELSE.
           l_excel_days = l_excel_days - 2.
         ENDIF.


      ADD l_excel_days TO e_date.