Formatting excel date field so Crystal reads it as date 1

[bnsSteve]

Hi,

I'm wondering what the best way to format an excel field that has date format as mm/dd/yyyy to so that crystal reads it as a date field and not date-time or string?

I've tried 'text to columns' making the field a date field, but crystal reads it as a date-time.

Reason being, I would prefer not to use a formula 'Date ({date.field})' to change the field to a date field, sometimes I get a 'bad-string' error when I continue to use other formulas.

Any suggestions is greatly appreciated, thanks!
steve.

Running Crystal 11 - 14.0.2.364

 

[Madawc]

Using Crystal with SQL, I've found that only DateTime is available for database fields.

there is an IsDate function. You could use this to filter out the 'bad string' errors.

Just what is it that gives 'bad string'? I'd have thought that anything that failed as date-time would also fail as date.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[bnsSteve]

Thanks for the reply Madawc,

Not exactly sure what gives the bad string error, my table field seem to be ok and when I create a formula 'date {date.field}' and drop it into the preview page it works fine. The problem arises when I use the formula in other forumulas, for instance, to return age. At first the formula will work but after refreshing it, the bad string error pops up.

But I find that if Crystal already reads the original date field as 'date' and I do not have to change the field type, I don't get any errors. Just not sure how to format the table so Crystal will read it just as a 'date' field.

Running Crystal 11 - 14.0.2.364

 

[Madawc]

So did you try what I advised?

You could also try showing intermediate results from a calculation that fails.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[bnsSteve]

Thanks Madawc! The ISDate function seems to be working better so I do not have to convert the excel field and do not appear to be getting any string errors so far.

Thanks again for the tip,
Steve.

Running Crystal 11 - 14.0.2.364