| Author |
Message |
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
  
Topic: Date Format
Posted: 13 Nov 2009 at 7:08am |
Crystal Report XI
I'm pulling in data to the Crystal Report from Excel 2007 where I have a column in Excel formatted as a 'Date' field.
However, when reviewing the Crystal Report, it displays the 'Date' field as 2009/09/24 00:00:00.00. When I bring up the CR Format Editor, I normally see the 'Date' tab, but see the 'Paragraph' tab instead. How can I display the 'Date' field to show as 9/24/2009? And why don't I see the 'Date' tab in the Format Editor?
|
 IP Logged |
|
|
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 13 Nov 2009 at 7:27am |
I tried this formula:
ToText({'Project_Expenditures_'.Date}, 'MM-dd-yyyy')
but an error message says 'Too many arguments given to this function' and highlights the 'MM-dd-yyy'
Any ideas?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Nov 2009 at 7:29am |
Can you alter the format in your source excel to MM/dd/yyyy ?
That is probably the easiest solution and likely crystal will make that a date field.
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 13 Nov 2009 at 7:44am |
I have the date in Excel formatted as 26-Aug-2009
and in CR it's formatted as ToText({'Project_Expenditures_'.Date}, 'dd-MMM-yyyy')
Still getting the same error. Excel doesn't give me an option to format date cells as MM/dd/yyyy, and I'm not sure what code I would use if I was to go into the Excel Visual Basic Code Editor.
I'm thinking of importing the Excel spreadsheet into Access, because I don't seem to have this problem when pulling dates from Access.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Nov 2009 at 7:48am |
In Excel you should be able to foermat the field as "*3/14/2001."
Use the Locale as English (U.S.) and it should be an available format. Edited by DBlank - 13 Nov 2009 at 7:49am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 13 Nov 2009 at 7:55am |
Excel is formatted as "*3/14/2001" and English (U.S.)
CR formula is ToText ({'Project_Expenditures_'.Date}, 'MM/dd/yyyy'), but still get the error message. Very frustrating. I'm sure it's something easy that I'm overlooking........
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Nov 2009 at 8:08am |
Is the field in crystal now a Date/Datetime field or still string?
If it is now a date/datetime field you can format it using the format field option instead of the totext formula.
If it is already a text field the totext() is redundant and the formating option is not allowed because that only applies to altering a date field (hence your error).
I was trying to get the source to pull in as a date field to avoid the whole conversion process.
If rarely use excel as a source so am not sure how to force it as a date coming in.
If it is coming in as a string we could try and convert it to a date so you can format that way... Edited by DBlank - 13 Nov 2009 at 8:10am
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 13 Nov 2009 at 8:44am |
The Format Editor in CR still shows the 'Paragraph' tab, instead of the 'Date' tab, so I'm assuming this is still a string.
I was able to change the Excel cell format to mm/dd/yyyy by using the 'Custom' category in the 'Format Cells' box, but once I did this, the date field in CR is now blank, even after reverting the date back to what I had.
This is strange because I can change data in other colums thats updated in the CR, but dates aren't there anymore.
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 13 Nov 2009 at 8:50am |
|
I closed and reopened both CR and Excel and the dates are not showing up in CR. I did notice that in the CR Field Explorer, the 'Date' field from the Excel spreadsheet is showing up as 'Date: DateTime' while other fields show up as String, Currency, and Number.
|
| IP Logged |
|
jgarner
Senior Member
Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
|
Posted: 13 Nov 2009 at 9:00am |
|
Somehow changing things around in CR seems to have the date showing up correctly now. There may have been an issue with unmapped fields from different columns of date in Excel. Thanks for being persistant with your help DBlank.
|
| IP Logged |
|
|
|
