Crystal reports and Excel

[junction123]

The field(time) is a datetime in the table. The values are retrieved from a SQL procedure.

The crystal report correctly displays the field as in eg below:
March 1, 1999 1:23 pm

However the exported Excel report displays the field as
3/1/1999 1:23 pm.

Can I use a conditional formula within the report so that the exported Excel report displays the field in the correct format?

Thanks in advance!
junction123

 

[Reebo99]

You can change the field type to text by using :

ToText({Table.Field},"MMMM d, yyyy h:mm tt&quot

Then it will export as a string.





Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[junction123]

This is what I am doing:

I right click on the field(time) and then Format Field

I click on DateTime. The format is (March 1, 1999 1:23 pm)

Then I click on Customize and then x-2 near Order.

I am getting an error in the formula saying "the formula result must be a number"

Where should I enter the ToText function?

Also, How can I get the time column witdth to expand automatically to display the entire value? Will a VB script that reformats the sheet after exporting and prior to opening Excel do the trick? I am unfamiliar with VB but I have heard that it can be accomplished.

Thanks in advance!
junction123

 

[Reebo99]

This is a new formula field ...

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[junction123]

Sorry for being a pain again! But in the Formula Editor, I get an error for the field name.

My report gets it's data from a whacky SQL Stored Proc. The original table from where the field in the report gets populated is a char(9) type field.

In the report itself, I have the field as datetime type.

Should I enter the original table and field name in the formula?

I am using the report name(tms_am_review) and the field name(time) in the report for the Table.Field values.

ToText({Table.Field},"MMMM d, yyyy h:mm tt&quot


Thanks!
junction123

 

[Reebo99]

Sorry :

ToText({Table.Field},"MMMM d, yyyy h:mm tt&quot

Replace the item in bold with your real datetime field.

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)