Recall-Help with lowest date in Excel !

[actcognosuser]

I  have a date that is set to lowest date in DB2 as 01-01-0001 .When i run the report in HTML it displays the date as is.When i run it in excel it displays as 01-00-1900.I used the cast(dataitem ,date).Is there something to do with the version of excel that I am using??

[sag4cognos]

Hello actcognosuser,

I had run into this issue once, such as this and I realized it was the locale settings in excel which was forcing the output to be changed. Might be of help to you.

Microsoft Excel date formulas can only use dates entered between 1/1/1900 and 12/31/9999.

Thanks,
Sagar

[actcognosuser]

Thanks so much for the reply Sagar. Do u know if there is a work around for this problem ?

Can this be resolved if I use a different version of excel?

[sag4cognos]

One solution that comes to my mind, which may or may fit your needs, is not to type cast the dataitem into a date. Rather you can cast it into a string, or do not cast it at all. Excel will not modify the value into date and you should be able to see the data as it is.

If you are thinking of further calculations based on that date, you can write a macro which can do the calculations for you. Again, please be advised. Excel will not check the validity of the dates if they do not fall into that range.  - 1/1/1900 and 12/31/9999.

Thanks,
Sagar