| Author |
Message |
MortyUK
Newbie
Joined: 08 Oct 2013
Online Status: Offline
Posts: 4
|
   Topic: Changing Date Format
Posted: 23 Oct 2013 at 10:17pm |
All
I'm building reports from excel spreadsheets produced from a 3rd party system which I don't have access to.
The data produced holds the date in the following format YYYY/MM/DD, how can I change this in crystal reports so the report shows this as a date and not just a number field.
Changing format from YYYY/MM/DD
to
DD/MM/YYYY or MM/DD/YYYY
|
|
Trying to get the Knowledge
|
 IP Logged |
|
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 24 Oct 2013 at 2:52am |
Hi MortyUK,
This is my first assist (so please bear with me). In order to change the date format I created a formula as date({Table.value}) I also set the Format Editor to System Default Short Format - but you can decide exacly from the list how you want it.
Hope I helped as it'll be a first :)
|
|
IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 24 Oct 2013 at 4:46am |
hI Try this : Cdate(Totext({Orders.Order Date},'MM/dd/yyyy')) note : replace orders.order date with your excel sheet date field
|
|
Thanks,
Sastry
|
|
IP Logged |
|
shabbaranks
Groupie
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
|
Posted: 24 Oct 2013 at 4:50am |
Originally posted by SastryhI
Try this :
Cdate(Totext({Orders.Order Date},'MM/dd/yyyy'))
note : replace orders.order date with your excel sheet date field
Just so I know - was my example incorrect? Its just that it worked for me - as did yours but I was wondering why you suggested different? Or is it your method is explicit to a format?
|
|
IP Logged |
|
MortyUK
Newbie
Joined: 08 Oct 2013
Online Status: Offline
Posts: 4
|
Posted: 24 Oct 2013 at 10:11pm |
Thanks to you both, I have tried the formula, but it gives me an error of bad number format string.
Looking at the imported information the dates are imported as number and not actual dates, will this have an effect
|
|
Trying to get the Knowledge
|
|
IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 24 Oct 2013 at 10:19pm |
hi I think the date which you are importing form excel would be in string format. Can place your mouse pointer on the report filed and see what datatype it is showing. Also you can right click on database expert and select show field type. To understand better, can you copy past your date data in your post ?
|
|
Thanks,
Sastry
|
|
IP Logged |
|
MortyUK
Newbie
Joined: 08 Oct 2013
Online Status: Offline
Posts: 4
|
Posted: 24 Oct 2013 at 10:30pm |
he is an example 20280901 is imported from the excel sheet to Crystal Reports as a number
Using the formula above I get the error of 'Bad Number Format'
If I changed the status of comment or uncomment (see below) I get no error and the formula is changed to a date and time cell, but I get no date in the cell, just blank
//Cdate(Totext({R06_CL9999_BR_DWH_IssFeed_cstat_.Cycle Date},'MM/dd/yyyy'))
|
|
Trying to get the Knowledge
|
|
IP Logged |
|
MortyUK
Newbie
Joined: 08 Oct 2013
Online Status: Offline
Posts: 4
|
Posted: 24 Oct 2013 at 11:51pm |
All I have found the solution in the formula below
date(mid(totext( {datefield},0,''),5,2)+'/'+ right(totext( {datefield},0,''),2) + '/'+ left(totext( {datefield},0,''),4))
This was from the following post http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11249 in September by DBlank
|
|
Trying to get the Knowledge
|
|
IP Logged |
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 25 Oct 2013 at 12:30am |
Hi Can you tell me out of this number which part is year, month and day 20280901 ? If you are trying to display only seconds then use below formula to display the date dateadd('s',20280901,date(1900,01,01)) Note : inplace of date number insert your database field
|
|
Thanks,
Sastry
|
|
IP Logged |
|
|
|
