topic Re: Date format format issue in Excel for different countries. in Application Development and Automation Discussions

Date format format issue in Excel for different countries.

Former Member — Mon, 14 Nov 2011 05:42:32 GMT

Here is the code in Z report for Date Format before downloading the file onto UNIX format.

The file generated on the UNIX has the file extension ".DAT".

The below code generates the Date Format into .... MM/DD/YYY ..this is for country US.

The issue is for other countries.

Data: lv_char TYPE string, "Converted value for data file

IF lx_fcat-inttype EQ 'D'.

IF <lfs_value> IS INITIAL.

CLEAR lv_char.

ELSE.

Convert date in user format

CALL FUNCTION 'CONVERT_DATE_TO_EXTERNAL'

EXPORTING

date_internal = <lfs_value>

IMPORTING

date_external = lv_char

EXCEPTIONS

date_internal_is_invalid = 0

OTHERS = 0.

ENDIF.

Failure Scenario:

When users change their Settings for Date Format to different country.

In Windows this is done from Control Panel~~> Regional & Langue~~-> Date Format (Example Germany).

Now when the users are running Z report and when the file is extracted onto UNIX (Example FILE.DAT), the

dates are coming inconsistent.

Example:

40087

10/18/2004

Re: Date format format issue in Excel for different countries.

Former Member — Mon, 14 Nov 2011 05:57:39 GMT

check in SU01D , for that user id

in Defaults tab..whats the Date format setup.

Re: Date format format issue in Excel for different countries.

Former Member — Mon, 14 Nov 2011 09:11:52 GMT

Hi,

You have issue in UNIX system or excel file. Please be clear on what is your requirement.

Regards

Senthil

Re: Date format format issue in Excel for different countries.

Former Member — Mon, 14 Nov 2011 18:16:59 GMT

The file is written on the Unix Folder with .DAT extension.

The file is downloaded from UNIX in 2 ways:

1)The file is then downloaded onto PC using CG3Y.

2)The .DAT file is moved to sharepoint and downloadd from there.

Re: Date format format issue in Excel for different countries.

gokul_radhakrishnan3 — Tue, 15 Nov 2011 09:39:42 GMT

Hi,

As Manjunath mentioned, it should be because of the SAP user settings & not because of the windows settings. The conversion is happening in the function module & CG3Y simply transfers the binary data. Check the 'own data' date format check menu system-> user profile-> own data

Regards,

Gokul

Re: Date format format issue in Excel for different countries.

Former Member — Wed, 16 Nov 2011 01:01:22 GMT

Hi ,

Its a strange requirement but still lookig for answers..

1) Tried the User Profile and it the file is being generated in the format the use has. However

if we open the excel file ( Right click and say format Cells---->In this case it shows GENERAL).....The user wants the cell

always in the date format so that can have sort and other macros...

To Reiterate................

1) We have to generate the Files in the date format that the user profiel has and also the excel file should show the date cells in date format but not general....