Changing Date Format
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20146
Printed Date: 05 May 2024 at 11:55pm
Topic: Changing Date Format
Posted By: MortyUK
Subject: Changing Date Format
Date 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
|
Replies:
Posted By: shabbaranks
Date 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 :)
|
Posted By: Sastry
Date 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
|
Posted By: shabbaranks
Date 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?
|
Posted By: MortyUK
Date 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
|
Posted By: Sastry
Date 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
|
Posted By: MortyUK
Date 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
|
Posted By: MortyUK
Date Posted: 24 Oct 2013 at 11:51pm
|
All I have found the solution in the formula below
date(mid(totext( mailto:%7b@date%7d,0,%29,5,2%29+/ - {datefield},0,''),5,2)+'/' + right(totext( mailto:%7b@date%7d,0,%29,2 - {datefield},0,''),2 ) + '/'+ left(totext( mailto:%7b@date%7d,0,%29,4 - {datefield},0,''),4 ))
This was from the following post http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11249 - http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11249 in September by DBlank
-------------
Trying to get the Knowledge
|
Posted By: Sastry
Date 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
|
|