Date shown with a different format in MS Excel spreadsheet

[former_member367551]

Dear forumers,

I have quite an odd problem with my report program. In this report, I will need to send an attachment file (.XLS) in an email before displaying the report output in ALV. The FM, 'SO_DOCUMENT_SEND_API1' is used to send the email.

Everything works perfectly except for a strange date format issue (only happens in the attachment file).

Sample Output (see the last line):-

10/22/2009                                                                                
09/30/2009                                                                                
02/23/2009
04/22/2009                                                                                
07/28/2009
03-05-09

In my program logic, I manually format all the dates (via concatenation) from YYYYMMDD to MM/DD/YYYY. And in debug mode, I checked that the last line in the internal table (see above) is also written as "03/05/2009". What could have possibly gone wrong here (the rest of the displayed date formats are fine)?

Please do help. Thanks.

Codes:-

" Create local string variables
  DATA:
  lv_user         TYPE string,
  lv_lockdate     TYPE string,
  lv_new_lockdate TYPE string.

  ...

  LOOP AT i_usr02[] INTO w_usr02.
" Get user ID and locked date
    lv_user     = w_usr02-bname.
    lv_lockdate = w_usr02-pwdlockdate.

    IF w_usr02-pwdlockdate IS NOT INITIAL.
" Format locked date
      PERFORM format_date CHANGING lv_lockdate
                                   lv_new_lockdate.

" Insert user ID and formatted locked date
      PERFORM concatenate_append USING lv_user
                                       lv_new_lockdate
                                       c_tab.
    ELSE.
" Insert user ID only
      PERFORM concatenate_append USING lv_user
                                       space
                                       c_tab.
    ENDIF.

" Clear string variables
    CLEAR: lv_user,
           lv_lockdate,
           lv_new_lockdate.
  ENDLOOP.

[former_member367551]

Dear forumers,

I have quite an odd problem with my report program. In this report, I will need to send an attachment file (.XLS) in an email before displaying the report output in ALV. The FM, 'SO_DOCUMENT_SEND_API1' is used to send the email.

Everything works perfectly except for a strange date format issue (only happens in the attachment file).

Sample Output (see the last line):-

10/22/2009                                                                                
09/30/2009                                                                                
02/23/2009
04/22/2009                                                                                
07/28/2009
03-05-09

In my program logic, I manually format all the dates (via concatenation) from YYYYMMDD to MM/DD/YYYY. And in debug mode, I checked that the last line in the internal table (see above) is also written as "03/05/2009". What could have possibly gone wrong here (the rest of the displayed date formats are fine)?

Please do help. Thanks.

Codes:-

" Create local string variables
  DATA:
  lv_user         TYPE string,
  lv_lockdate     TYPE string,
  lv_new_lockdate TYPE string.

  ...

  LOOP AT i_usr02[] INTO w_usr02.
" Get user ID and locked date
    lv_user     = w_usr02-bname.
    lv_lockdate = w_usr02-pwdlockdate.

    IF w_usr02-pwdlockdate IS NOT INITIAL.
" Format locked date
      PERFORM format_date CHANGING lv_lockdate
                                   lv_new_lockdate.

" Insert user ID and formatted locked date
      PERFORM concatenate_append USING lv_user
                                       lv_new_lockdate
                                       c_tab.
    ELSE.
" Insert user ID only
      PERFORM concatenate_append USING lv_user
                                       space
                                       c_tab.
    ENDIF.

" Clear string variables
    CLEAR: lv_user,
           lv_lockdate,
           lv_new_lockdate.
  ENDLOOP.

[former_member367551]

Further codes (subroutines):-

DATA i_attach       TYPE STANDARD TABLE OF solisti1.

...

"&---------------------------------------------------------------------*
"&      Form  FORMAT_DATE
"&---------------------------------------------------------------------*
"       Subroutine to format date ( MM/DD/YYYY )
"----------------------------------------------------------------------*
"      --> PV_DATE        Date ( YYYYMMDD )
"      <-- PV_NEWDATE     Date ( MM/DD/YYYY )
"----------------------------------------------------------------------*
FORM format_date  CHANGING pv_date    TYPE string
                           pv_newdate TYPE string.

" Condense date string first
  CONDENSE pv_date.

" Concatenate portions of date string accordingly
  CONCATENATE pv_date+4(2) pv_date+6(2) pv_date+0(4)
    INTO pv_newdate
    SEPARATED BY c_slash.

ENDFORM.                    " FORMAT_DATE

"&---------------------------------------------------------------------*
"&      Form  CONCATENATE_APPEND
"&---------------------------------------------------------------------*
"       Subroutine to concatenate two strings and a new line
"       and finally appending it to the internal table
"----------------------------------------------------------------------*
"      --> PV_STR_1     First string variable
"      --> PV_STR_2     Second string variable
"      --> PV_FLAG      Flag (TAB or SPC indicator)
"----------------------------------------------------------------------*
FORM concatenate_append  USING    pv_str_1 TYPE string
                                  pv_str_2 TYPE string
                                  pv_flag  TYPE char3.

" Concatenate two string parameters
  IF pv_flag = c_space.
    CONCATENATE pv_str_1 pv_str_2
      INTO w_attach_tmp
      SEPARATED BY space.
  ELSEIF pv_flag = c_tab.
    CONCATENATE pv_str_1 pv_str_2
      INTO w_attach_tmp
      SEPARATED BY c_htab.
  ENDIF.

" Insert a carriage return + line feed as well
  CONCATENATE c_crlf w_attach_tmp INTO w_attach.

" Append workarea to table before clearing workareas
  APPEND w_attach TO i_attach.
  CLEAR: w_attach,
         w_attach_tmp.

ENDFORM.                    " CONCATENATE_APPEND

[Former Member]

excel has its own date format.

After you import the data into internal table, loop at the table and convert date back to YYYYMMDD format using the FM : CONVERT_DATE_TO_INTERNAL

Edited by: imran khan on Oct 29, 2009 9:07 AM

[Former Member]

Hi,

Change the data type of date from STRING to sy-datum and check the output.

Regards

VEnk@

[former_member367551]

The issue has been resolved by just adding =" at the beginning and " at the end of the date string. Thanks!

[Former Member]

Hello There ,

I am too facing similiar problem in my object .

I am sending as YYYY-MM-DD in the attachement and while downloading the data , i find date in MM/DD/YYYY format.

Please let me know you exactly you solved the issue .

Regards,

Abhi..

[Former Member]

hello all ,

I am putting my code for the reference purpose .

lv_date_from , gs_output-start_date is of string type .

CONCATENATE '="'

gs_output-start_date(4)

gc_seperator

gs_output-start_date+4(2)

gc_seperator

gs_output-start_date+6(2)

'"'

INTO lv_date_from. " SEPARATED BY gc_seperator.

gs_output-start_date = lv_date_from.

now the format YYYY-MM-DD never gets effected while format and is always remain as it is while even after the download .

Regards,

Abhi..