Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Exporting data from sap to excel (date with decimals)

Former Member
0 Kudos
1,623

Hi,

when a file is exported from SAP, excel displays dates as decimal numbers (i.e. 0,401771)

Cannot understand where is the problem

Thanks

Best regards

1 ACCEPTED SOLUTION

Former Member
0 Kudos
393

Hi Guest,

Could you try checking to see what format the date is stored in within SAP?

i.e. if you store the date field with data type DATS instead of char10, then you should get the desired result.

Alternatively in Excel just format the column to have the date format you would like ot create a macro to do that.

Does that help?

15 REPLIES 15

Former Member
0 Kudos
394

Hi Guest,

Could you try checking to see what format the date is stored in within SAP?

i.e. if you store the date field with data type DATS instead of char10, then you should get the desired result.

Alternatively in Excel just format the column to have the date format you would like ot create a macro to do that.

Does that help?

arindam_m
Active Contributor
0 Kudos
393

Hi,

Check the excel column formatting could be solved by changing the formatting in excel after download or typecast the data type of the column to Character before download.

Cheers,

Arindam

Former Member
0 Kudos
393

Hi,

I'm downloading to excel from a standard report

arindam_m
Active Contributor
0 Kudos
393

Hi,

In that case you might have to format the excel columns a bit, if there is no way to change the standard SAP code.

Cheers,

Arindam

Former Member
0 Kudos
393

Hi,

Check the excel column format settings.

and try to download and check on another system.

Regards,

Ravi Pratap SIngh

former_member184569
Active Contributor
0 Kudos
393

c_dat(10) TYPE c,


WRITE date TO c_dat DD/MM/YYYY.

Now include this field c_dat instead of the date field in your export table.

Former Member
0 Kudos
393

Hi!

Please try this:

su01 =>  <give user ID> & execute  => click on tab 'Defaults' =>choose Date format

Regards,

KS

Former Member
0 Kudos
393

Hi.

From Which Transaction code  you are getting the data.

If it is a standard TC and if it is not possible for you to edit.

Write MACRO in excel and change the data to date format Temporarily.

If it is Custom report. Check the data type of the field . give it as DATS.

Former Member
0 Kudos
393

HI,

Please check the date type in your program final internal table.

try :fkdat type  vbrk-fkdat.

This will solve your problem.

Regards,

Ravi Pratap Singh

Former Member
0 Kudos
393

The computer runs Windows XP with SAP GUI 7.2, I updated the GUI to 7.3 but won't work

0 Kudos
393

Hi Guest,

Could you check that you have the following note applied to your SAP system:

972579 (XXL: In Office 2007 it's not possible to set range "DAT1")

Applying this note should fix this problem for you - try in your Dev system and see.

0 Kudos
393

Hi,

I tried by trx snote but is not implementable in our system

0 Kudos
393

Hi Guest,

Could you please explain why you can't implement the note..?

Is is becasue the note is already applied in your system?

Also, could you tell us which version of Excel you are using?

Former Member
0 Kudos
393

Hi,

I don't know you are using the Z transaction to download the data from sap(means internal table) to excel. If so use FM " GUI_DOWNLOAD " download to excel as below...

CALL FUNCTION 'GUI_DOWNLOAD'
       EXPORTING

          FILENAME                        = 'DDD.xls'
          FILETYPE                        = 'ASC'
          APPEND                          = 'X'
          WRITE_FIELD_SEPARATOR           = 'X'

     TABLES
          DATA_TAB                        = ITAB.

where " ITAB" is your internal table(assume). If you get the date as it is into excel first you need to declare date field as "string" like

DATA:     BEGIN OF ITAB OCCURS 0,

                    FIELD_NAME TYPE STRING,

               END OF ITAB.

Now after fetching the data from SAP tables to your final internal table then process that final internal table into ITAB. While populating the final internal table data into ITAB do following conversion to get the date as it is into excel.

CONCATENATE   IT_BSIK-BUDAT+06(02)  '.'

                                 IT_BSIK-BUDAT+04(02)  '.'

                                 IT_BSIK-BUDAT+00(04)           INTO ITAB-BUDAT.

finally pass that ITAB internal table to FM(mentioned above). Now check your excel.

Regards,

R.S.K





Former Member
0 Kudos
393

Hi,

How are you exporting this report?

Are you creating a local xcl sheet and then exporting OR while exporting you are creating a xcl?

Do not give an existing xcl sheet, rather, while exporting, give a new excel sheet name and create, and then see if it works.

Regards,

KS