19000100 when date is zero exporting to excell

[Gonfer]

Hi guys!!!

I've designed a report containing a column with data type DATE cutomized as YYYYMMDD without "/" (only digits), It works in Crystal perfectly, when there is any date to show, and when there is no, shows blank, but when I export to Excell in DATA ONLY mode, the column with DATE data type, for many lines show me 19000100 instead "blank" when there is no date to show (Data = zero) in the Excell spreadsheet.

I'll appreciate if somebody can help me.

Thanks in advance!!!


GonFer

 

[SkipVought]

hi,

show me 19000100 instead "blank"

It appears that your DATA ONLY converter is actually changing the STRING of digits (yyyymmdd) to a Date Serial NUMBER, so that when the converter encounters a NULL value to convert to a Date Serial Number, it is converting to to a ZERO Date Serial Number, which Excel can then FORMAT to display 19000100 or January 0 1900.

So is there a problem with this, or does it just bug the heck out of you and you'ld rather not see it like that? Believe me, that it is much easier to manipuate date data in Excel with ZEROS rather than NULLS.

Please do this to verify that what I have stated is true or not:
1. SELECT the column containing these imported dates
2. Change the Number Format from Custom to GENERAL

All your dates should become numbers, well not really becuase they already ARE numbers, but the DISPLAY changes to numbers, and then hit the UNDO icon to change the DISPLAY back to what it was.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gonfer]

Thanks SkipVought !! for your response

You are right those are values for the date column, but I am applying a formula field in my crystal report design for this column:

**********************************************************************************
If Maximum ({eePayStatus.idPayStatus}, {eeKeys.eeEEnum}) = "T" Then
Maximum ({eePayStatus.TerminatedDate}, {eeKeys.eeEEnum})
**********************************************************************************

Only when the value is 'T', (Terminated), the report get and shows the date termination else "nothing", so I am not getting any other data value from the tables for the records not having value 'T'.

On my design, I've tried to use an overlaped formula field over the first one showed lines up :

***********************************************************************************
If Maximum ({eePayStatus.idPayStatus}, {eeKeys.eeEEnum}) <> "T" Then
" "
***********************************************************************************
But I get the same result, How can I to avoid that?. the problem is that this reports is run and export to excel by my manager every day, and he doesn't know much of Excell, I am looking for an automated solution as I can get it now. That's the reason why I am trying to solve this issue in crystal, before exporting to Excel.

Some suggestion?

Thanls in advance

GonFer

 

[SkipVought]

It is one of the liabilities of transferring data from one system to another.

I NEVER export data from Business Objects as Excel data, because I know that undesired data transofmations can take place.

Does your manager need the data in Excel? Why not a PDF?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gonfer]

SkipVought

He he hates pdf format . He must to send this report after look at it and send it converted in csv format. You know? If I export this report in Ms Excel standar (it means NOT DATA ONLY), the column dates works perfectly! but in this standar mode, there are problems with the cvs format.

What would you suggest me for doing after exporting the report with DATA ONLY mode data in Excel? I I convert to general format all the column date becames in zeros with exception of valid dates, they are converted in general format too, maybe some conditional for using from your experience?

Thanks!!!

Gonfer

 

[SkipVought]

there are problems with the cvs format.

What problems?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]