How to deal with the Date Format in the Excel

[Former Member]

Dears,

I have met an odd problem in my program, I pass the internal table value to an excel, and send mail with excel attachment,

but the date format, I use it as this in my program,

data: lv_date type c length 10.
write gt_vbak-erdat to lv_date.

concatnate  lv_date   contab
 con_tab into lt_contents_bin-line.

  concatenate con_cret lt_contents_bin-line
  into lt_contents_bin-line.
  append  lt_contents_bin.
  clear   lt_contents_bin.

then use

FM  'SO_NEW_DOCUMENT_ATT_SEND_API1'

to send mail with excel attachment.

The problem is that when i see the excel the date format seems like '#######' in the excel tab, and if i broaden the excel tab,

then it appears the right date and right format , like '07/22/2009",

I know there is no fault in data, but only a problem with the appearance of the date format once I open the excel.

just like '07/22/2009', not '######'

But i want to solve this problem , anybody could help me?

Thanks a lot.

Julie

[former_member209217]

It seems to be the problem with the settings in Excel Sheet.

[Former Member]

Hi,

Surely there is no data error.

To resolve this issue what you can do is. just create a larger (>10 char) column heading.

I hope in excel output the first row would be the column heading and for the date field you must have defined 'Date'.

So instead of 'Date' put some bigger colum heading at least 11 char.

and also increase the size of lv_date by 1 character.

Hope this will solve your problem.

Regards,

Raj Gupta

[Former Member]

hi,

I set lv_date type c length 12, but not work.

seems it not a problem with the length with the type of lv_date.

[Former Member]

its standard excel functionality..

if u keep as 04.02.2009 it will come poperly.

but if you keep as 04/02/2009 it will come as ### if the length is not poper

[Former Member]

Hi,

If you still dint find a solution try this. Append a apostophe symbol to the date column alone in the internal table and then pass it to

FM 'SO_NEW_DOCUMENT_ATT_SEND_API1' so that excel will consider the date column as text and display exactly what u have in the internal table

Constants: c_quot(4)     type c value ''''.        " For value '
data: lv_date type c length 10.
write gt_vbak-erdat to lv_date.
 
concatnate  c_quot lv_date   contab         " Add the apostrophe here
 con_tab into lt_contents_bin-line.
 
  concatenate con_cret lt_contents_bin-line
  into lt_contents_bin-line.
  append  lt_contents_bin.
  clear   lt_contents_bin.

Regards,

Vik