topic Re: Incorrect Date format in Application Development and Automation Discussions

Incorrect Date format

Former Member — Thu, 25 Jul 2013 05:01:54 GMT

Hi experts,

I am sending data from my SAP report to Excel file. But data in excel file is not showing dates in correct format.

Date is in stored in Char . But is excel it is coming as mm/dd/yyyy format.

Why is Char value changing to date format.

How can i solve this problem??

Regards,

Abap user

Re: Incorrect Date format

Former Member — Thu, 25 Jul 2013 11:17:28 GMT

Hi Meenu,

You may need to format the date column in the excel sheet, thought it sounds strange.

Just check the internal table that is exported to excel once again.

If its a character field like data(8) type c, it should come in excel also as yyyymmdd.

Re: Incorrect Date format

gaurab_banerji — Thu, 25 Jul 2013 11:25:29 GMT

convert the incoming data from excel by splitting the characters on '/' into month, day and year

write something like...

split lwa_excel-date at '/' into lv_month lv_day lv_year. (MM/DD/YYYY format from excel string)

concatenate lv_year lv_month lv_date into lwa_table-date. (YYYYMMDD format on table)

Re: Incorrect Date format

Former Member — Thu, 25 Jul 2013 11:26:59 GMT

Hi Meenu,

Change the date format in excel sheet. It cant be controlled using Function Module in SAP.

Thanks & Regards,

Akshay

Re: Incorrect Date format

Former Member — Thu, 25 Jul 2013 13:35:54 GMT

Hi Meenu,

Sometimes the excel automatically converts the date into its preset format.

The format can be seen/changes in the path specified below:

Select all the cells (Ctrl+A) - Right Click - Format Cells - Select Date on the left hand menu

Now you can see the current format and all the available formats.

Hope this helps!

Re: Incorrect Date format

Former Member — Thu, 25 Jul 2013 13:38:02 GMT

Hi , if you do not want the Excel to format the date, concatenate ' before date, then it will consider the field value as text and not any other format.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: Incorrect Date format

Former Member — Thu, 25 Jul 2013 13:42:27 GMT

Hello Meenu,

use the following code..

It will give convert date in correct format.

e.x. 20130725 (25 july 2013) will come in 25/07/2013.

DATA:
         lv_date4      TYPE string,
         lv_date1      TYPE char2,
         lv_date2      TYPE char2,
         lv_date3      TYPE char4,
         lv_len        TYPE i.

et_date type string.

"IT_date is having date input.

lv_len = strlen( it_date ).

et_date = it_date.

IF it_date IS NOT INITIAL .

IF lv_len EQ 8.

       CLEAR et_date.
       lv_date4 = it_date.
       lv_date3 = lv_date4+0(4).
       lv_date2 = lv_date4+4(2).
       lv_date1 = lv_date4+6(2).
       CONCATENATE lv_date1 lv_date2 lv_date3 INTO et_date SEPARATED BY '/'.
       CLEAR : lv_date3, lv_date2,lv_date1,lv_date4.
     ENDIF.
   ENDIF.

Or Change excel by (Ctrl+A) - Right Click - Format Cells - Select Date on the left hand menu

Chandra Agarwal..

Re: Incorrect Date format

former_member585060 — Thu, 25 Jul 2013 13:43:05 GMT

Hi,

Are you downloading an ALV output or Normal report output to Excel sheet? The same date field. Just check your logic while preparing the excel sheet table.

Declare the date field as datum(8), then when moving to excel internal table use WRITE statement instead of MOVE.

WRITE : wa_table-date TO wa_excel-date. " wa_table-date is datum(8) and wa_excel-date is char 10.

Thanks & Regards

Bala Krishna

Re: Incorrect Date format

Former Member — Fri, 26 Jul 2013 07:46:39 GMT

I dont want to change format in excel. I want my code to send data in excel in right format.

Re: Incorrect Date format

Former Member — Fri, 26 Jul 2013 07:52:52 GMT

In my code i am using :

SET PROPERTY OF p_cell 'Value' = <lfs_tab>-yvalue .

here <lfs_tab>-yvalue is of type Char(255) and say its value is 10/01/2013.

(date is 10th Jaunary)

In excel is it showing 01/10/2013.

whereas when value of here <lfs_tab>-yvalue is 14/01/2013 i.e, 14th January

In excel it is showing 14//01/2013.

Re: Incorrect Date format

Former Member — Fri, 26 Jul 2013 08:11:41 GMT

Chnaging format of cell in excel file is the solution for my problem. I want data to be sent to excel in dd/mm/yyyy format by SAP system only.

Re: Incorrect Date format

former_member209120 — Fri, 26 Jul 2013 09:03:54 GMT

Hi Meenu Agarwal,

See this code

TYPE-POOLS slis.
TABLES : mkpf.
TYPES : BEGIN OF ty_mkpf,          "Header: Material Document
         mblnr TYPE mkpf-mblnr,     "Number of Material Document
         budat TYPE mkpf-budat,     "Posting Date in the Document
         mjahr TYPE mkpf-mjahr,     "Material Document Year
         END OF ty_mkpf.

TYPES : BEGIN OF ty_mseg,           "Document Segment: Material
         mblnr TYPE mseg-mblnr,      "Number of Material Document
         matnr TYPE mseg-matnr,      "Material Number
         END OF ty_mseg.

TYPES : BEGIN OF ty_makt,           "Material Descriptions
         matnr TYPE makt-matnr,      "Material Number
         maktx TYPE makt-maktx,      "Material Description (Short Text
         END OF ty_makt.

TYPES : BEGIN OF ty_final,
         mblnr TYPE mkpf-mblnr,     "Number of Material Document
         budat TYPE char255,     "Posting Date in the Document
         mjahr TYPE mkpf-mjahr,     "Material Document Year
         matnr TYPE mseg-matnr,     "Material Number
         maktx TYPE makt-maktx,     "Material Description (Short Text
         END OF ty_final.

*INTERNAL TABLES DECLARATIONS.

DATA : it_mkpf TYPE TABLE OF ty_mkpf,
        it_mseg TYPE TABLE OF ty_mseg,
        it_makt TYPE TABLE OF ty_makt,
        it_final TYPE TABLE OF ty_final.

*WORK AREA DECLARATAION

DATA : wa_mkpf TYPE ty_mkpf,
        wa_mseg TYPE ty_mseg,
        wa_makt TYPE ty_makt,
        wa_final TYPE ty_final.

*INTERNAL TABLE & WORK AREA FOR FIELDCAT

DATA : it_fieldct TYPE slis_t_fieldcat_alv,
        wa_fieldcat TYPE LINE OF slis_t_fieldcat_alv.

*LAYOUT DECALRATION

DATA : wa_layout TYPE slis_layout_alv.

*SELECTION SCREEN

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS : s_budat FOR mkpf-budat.
SELECTION-SCREEN : END OF BLOCK b1.

*START OF SELECTION

SELECT mblnr
        budat
        mjahr
        FROM mkpf INTO TABLE it_mkpf
        WHERE budat IN s_budat.

IF it_mkpf IS INITIAL.
   MESSAGE : 'No Data Found' TYPE 'S'.
   LEAVE LIST-PROCESSING.
ENDIF.

IF it_mkpf IS NOT INITIAL.

   SELECT mblnr
          matnr
          FROM mseg INTO TABLE it_mseg
          FOR ALL ENTRIES IN it_mkpf
          WHERE mblnr = it_mkpf-mblnr.

ENDIF.

IF it_mseg IS NOT INITIAL.

   SELECT matnr
          maktx
          FROM makt INTO TABLE it_makt
          FOR ALL ENTRIES IN it_mseg
          WHERE matnr = it_mseg-matnr
          AND   spras = 'EN'.
ENDIF.

*FILLING FINAL TABLE

LOOP AT it_mkpf INTO wa_mkpf.
   wa_final-mblnr = wa_mkpf-mblnr.
* wa_final-budat = wa_mkpf-budat.

   write wa_mkpf-budat to wa_final-budat using edit mask '__/__/____'.
   wa_final-mjahr = wa_mkpf-mjahr.

   READ TABLE it_mseg INTO wa_mseg WITH KEY mblnr = wa_mkpf-mblnr BINARY SEARCH.

   IF sy-subrc = 0.
     wa_final-matnr = wa_mseg-matnr.
   ENDIF.

   READ TABLE it_makt INTO wa_makt WITH KEY matnr = wa_mseg-matnr BINARY SEARCH.

   IF sy-subrc = 0.
     wa_final-maktx = wa_makt-maktx.
   ENDIF.

   APPEND wa_final TO it_final.
   CLEAR wa_final.
ENDLOOP.

*FIELDCAT DECLARATION

CLEAR wa_fieldcat.
wa_fieldcat-fieldname = 'MBLNR'.
wa_fieldcat-col_pos = '1'.
wa_fieldcat-seltext_m = 'Material Document No'.
APPEND wa_fieldcat TO it_fieldct.

CLEAR wa_fieldcat.
wa_fieldcat-fieldname = 'MJAHR'.
wa_fieldcat-col_pos = '2'.
wa_fieldcat-seltext_m = 'MaterialDocumentYEAR'.
APPEND wa_fieldcat TO it_fieldct.

CLEAR wa_fieldcat.
wa_fieldcat-fieldname = 'MATNR'.
wa_fieldcat-col_pos = '3'.
wa_fieldcat-seltext_m = 'Material No'.
APPEND wa_fieldcat TO it_fieldct.

CLEAR wa_fieldcat.
wa_fieldcat-fieldname = 'MAKTX'.
wa_fieldcat-col_pos = '4'.
wa_fieldcat-seltext_m = 'Material Description'.
APPEND wa_fieldcat TO it_fieldct.

CLEAR wa_fieldcat.
wa_fieldcat-fieldname = 'BUDAT'.
wa_fieldcat-col_pos = '5'.
wa_fieldcat-seltext_m = 'Posting Date'.
APPEND wa_fieldcat TO it_fieldct.

wa_layout-zebra = 'X'.
wa_layout-colwidth_optimize = 'X'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
*   I_INTERFACE_CHECK                 = ' '
*   I_BYPASSING_BUFFER                = ' '
*   I_BUFFER_ACTIVE                   = ' '
*   I_CALLBACK_PROGRAM                = ' '
*   I_CALLBACK_PF_STATUS_SET          = ' '
*   I_CALLBACK_USER_COMMAND           = ' '
*   I_CALLBACK_TOP_OF_PAGE            = ' '
*   I_CALLBACK_HTML_TOP_OF_PAGE       = ' '
*   I_CALLBACK_HTML_END_OF_LIST       = ' '
*   I_STRUCTURE_NAME                  =
*   I_BACKGROUND_ID                   = ' '
*   I_GRID_TITLE                      =
*   I_GRID_SETTINGS                   =
     is_layout                         = wa_layout
     it_fieldcat                       = it_fieldct
*   IT_EXCLUDING                      =
*   IT_SPECIAL_GROUPS                 =
*   IT_SORT                           =
*   IT_FILTER                         =
*   IS_SEL_HIDE                       =
*   I_DEFAULT                         = 'X'
*   I_SAVE                            = ' '
*   IS_VARIANT                        =
*   IT_EVENTS                         =
*   IT_EVENT_EXIT                     =
*   IS_PRINT                          =
*   IS_REPREP_ID                      =
*   I_SCREEN_START_COLUMN             = 0
*   I_SCREEN_START_LINE               = 0
*   I_SCREEN_END_COLUMN               = 0
*   I_SCREEN_END_LINE                 = 0
*   I_HTML_HEIGHT_TOP                 = 0
*   I_HTML_HEIGHT_END                 = 0
*   IT_ALV_GRAPHICS                   =
*   IT_HYPERLINK                      =
*   IT_ADD_FIELDCAT                   =
*   IT_EXCEPT_QINFO                   =
*   IR_SALV_FULLSCREEN_ADAPTER        =
* IMPORTING
*   E_EXIT_CAUSED_BY_CALLER           =
*   ES_EXIT_CAUSED_BY_USER            =
   TABLES
     t_outtab                          = it_final
* EXCEPTIONS
*   PROGRAM_ERROR                     = 1
*   OTHERS                            = 2
           .
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

output

Excel

Re: Incorrect Date format

gaurab_banerji — Fri, 26 Jul 2013 12:10:21 GMT

its not an abap issue, the problem is in the excel sheet.

the default format for excel is US format which is MM/DD/YYYY. so your dates need to be in that format.

In case you type in 14/01/2013 (since month 14 is obviously not possible) it stays as a character string field and field does not auto convert to a date field. you can check this by right clicking on the field and check format cells

in case you want to use DD/MM/YYYY you need to format the cell to UK date system first. i suggest you format the column to Uk date first before filling data.

Re: Incorrect Date format

Former Member — Tue, 15 Apr 2014 19:14:13 GMT

Hello all

I have a similar problem, some of my users are able to see correct data when downloading date data reports from SAP to excel others see numbers instead of the date.

I tried looking at Windows date settings...but nothing...

any idea how to fix this problem?