date formatting error when exporting into excel

[zb12]

I have reports that are scheduled to run in single sheet excel format. When the emails are sent and the recipient opens the attachment the report shows the date as what seems like a random set of numbers. However when the data is pasted into an excel spreadsheet the numbers convert to a date. For example 33592=12/20/91. also sometimes I run a report manually and I get the same problem. when i format the cells in excel to show is dates the numbers are converted. I have set the data format in cognos as a date format but the problem persists. Any ideas as to what's going on and how to fix it?

[johnpenna]

cast as a date

cast([Data item],date)

[Gopinath]

try placing the Data item inside a block.

[dumbell]

did you try to_char

[zb12]

i tried the first two and i still get the same thing. how do i use _char?

[CognosPaul]

The problem you're having is Excel converting the dates to numbers. Excel handles numbers by counting the number of days since January 0, 1900. 1/1/1900 = 1, 33592=12/20/91, 39945 = 5/12/09. This leads me to believe it's an Excel issue. Cognos is sending it in DATE format, Excel is reading it wrong.

A workaround might be formatting the date as text.

Code Select Expand

cast ([Date],VARCHAR(10))

[zb12]

formatting the date as text in excel?

[zb12]

the error is also occuring when i send the report in single sheet and it is opened in html in IE.

[CognosPaul]

In the query level in Cognos.

[zb12]

the weird thing is that when i open the html attachment, that was sent in the email from the report i scheduled, and then i copy and paste the date striaght into excel the data converts itself to a date instead of the number of days since 1900. i'm confused.

i tried setting the format as text in cognos and still the html attachment is not formatted correctly. i have just been telling the recipeints to copy and paste the data into excel as the only fix?

Thanks for your help

[CognosPaul]

Did you change the query item, or just the data format in the report? My suggestion was to modify the query item.

[zb12]

oh i just changed the data format. I'll go back and try your suggestion