topic Re: Date format error in excel sheet in Application Development and Automation Discussions

Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 05:31:57 GMT

When i try to download ALV grid display report into excel sheet using the following procedure

Menu - List - Export - SpreadSheet - Radiobutton Table - Radiobutton Microsoft Excel- Then an excel sheet is downloaded but the date format is of some random number like 40182 but actual date is (01/04/2010 format MM/DD/YYYY) .

But when i try to download using the procedure Application Menu bar - Local File - Radiobutton Spread Sheet - Giving the path of the file. When i see the date is in (01/04/2010 format MM/DD/YYYY) which is correct.

I want to know what are the settings that needs to be done for downloading the excel sheet with the first procedure which i mentioned above.

Regards

Venk@

Re: Date format error in excel sheet

GauthamV — Mon, 13 Sep 2010 06:05:01 GMT

Check the data declaration for your date field, because if you observe any standard transactions

the date will be same in both the methods to download data into exel.

Re: Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 06:09:50 GMT

Hi Venkat Reddy.

Change the Date format in Excel By selectinf field -> right click -> format Cells -> Number Tab -> Date -> Select format.

Re: Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 06:13:44 GMT

Hi Gautham,

I took standard types..

TYPES: BEGIN OF ty_bkpf,

gjahr TYPE bkpf-gjahr, "Fiscal Year

bldat TYPE bkpf-bldat, "Doc Date

usnam TYPE bkpf-usnam, "User Name

waers TYPE bkpf-waers, "Currency

END OF ty_bkpf.

No data declarations of Date down separately . You can see Doc date i have decalred as BKPF-BLDAT. Anything more you wanna add.

Regards

VEnk@

Re: Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 06:16:45 GMT

Hi Murthy,

I can see that if i format the cell to DATE then it will chg number to date format. But before downloading itself i want to download into DATE format.

Regards

VEnk@

Re: Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 06:29:34 GMT

Change the Date format in Excel By selectinf field -> right click -> format Cells -> Number Tab ->

custom->

then write dd/mm/yyyy

Edited by: kk.adhvaryu on Sep 13, 2010 8:29 AM

Re: Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 06:31:51 GMT

you can change the date format in your USER profile.., then download the report output and see.

Re: Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 06:36:41 GMT

Hi I have mentioned the same as of Murthy's reply.

I can do that by selecting the column and right click format the cell and to date format. But i want to download the file by default if it is date then it should download to date format. By your suggestion all the time user should change the format when he download the file which is not good.

Regards

VEnk@

Re: Date format error in excel sheet

Former Member — Mon, 13 Sep 2010 06:37:41 GMT

Hi i have checked the USer Profile date it is MM/DD/YYYY.

I'm able to download the file with the second procedure mentioned by me in my first post correctly. Since the date format is MM/DD/YYYY. But i'm more anxious to know wht went wrong when i download with the procedure using second download or wht needs to be done.

Regards

VEnk@

Edited by: Venkat Reddy on Sep 13, 2010 12:08 PM

Edited by: Venkat Reddy on Sep 13, 2010 12:13 PM

Re: Date format error in excel sheet

GauthamV — Mon, 13 Sep 2010 13:52:43 GMT

Can you post the code for field catelog that you defined in the program for that date field.

In most of the cases we get problem either because of data declarations or field catelog.

Re: Date format error in excel sheet

Former Member — Fri, 17 Sep 2010 14:52:11 GMT

Hi Venk@!

We have exactly the same symptom.

But we've used this function for years in many programs, and till now it was OK.

So - in my opinion - this problem mustn't caused by a program error.

However recently we changed Excel2003 to 2007, and SAPGUI to 710 also! (We have SAP 4.6C)

We've not found the solution yet, because the problem was indicated just now, and as I've started to look after this, I've just found immediately Your forum note.

So now we also looking after the solution, and please inform each other if it will be found.

Regards

Gyula Krupanszki

Re: Date format error in excel sheet

santosh_dv — Sun, 19 Sep 2010 18:08:46 GMT

Hello Venkat,

Change the format of the cell in excel sheet in which date is getting downloaded to your desired date format.

Right click on the date cell and choose format options ->Date.

This will solve your problem.

Thanks & Regards

Santosh

Re: Date format error in excel sheet

Former Member — Mon, 06 Feb 2012 16:27:30 GMT

Hi @Venka @Gyula Krupanszki

We have just upgraded from Excel 2003 to Excel 2010 and now we get the same problem as you with the dates appearing as numbers when we export ALV grid to Excel. SAP GUI is 710. It is only Excel that has changed. Did you find a solution?

EDIT:

We found the solution.

Check OSS note 972579

Edited by: Wayne Giles on Feb 7, 2012 11:54 AM

Re: Date format error in excel sheet

Former Member — Wed, 08 Feb 2012 09:25:27 GMT

Hi,

You have declared the data type as date for fetching the data.

The data type of the field in the ALV output table should be of data type date. Then the date will come in the format as you require.

If the data type is of char the value will come as number in excel.

Try with this and it will work.

Regards,

Konda Sravanthi.