Link to home
Start Free TrialLog in
Avatar of JamesJMcDonnell
JamesJMcDonnellFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Formula to format date string field in "DD/MM/YYY" to match date range parameter in Crystal Reports

I have a date field held in a string in "DD/MM/YYY"  format and want to use it in record selection criteria to match a date range parameter. I've tried converting it to a formula using:

Right ({Transit Analysis PO Details.CompletedDate},4 ) + '/' + Left(Right ({Transit Analysis PO Details.CompletedDate},4),2) + '/' + Left ({Transit Analysis PO Details.CompletedDate},2 )

but this does not work. Hoshould I change the formula to get it to work or do I need to take a different approach?
ASKER CERTIFIED SOLUTION
Avatar of bdreed35
bdreed35
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JamesJMcDonnell
JamesJMcDonnell
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

It's DD/MM/YYYY. I'm only using it in Crystal having formatted it from a SQL Server database datefield in a DateView. I had tried mid earlier and when it didn't work found the Left(Right use in an answer on this site. Was wondering if there might be a problem with leading zeroes being dropped for dates like 01/06/2006 and that someone might  have formula making use of Ltrim etc. Thanks, bdreed95, I will try your version using Date and Val when I get back to my work PC to-morrow morning.
Avatar of JamesJMcDonnell
JamesJMcDonnell
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks BDreed35 your code works. I have  a connected problem now. I want to display the date range on the report and using a normal formula for this,
e.g. minimum({?DateRange}) & " to " & maximum({?DateRange}) the date comes out in MM/DD/YYYY format. This is not so easy to reaarange as leading zeroes are dropped as in e.g. 6/1/2006 to 6/30/2006. How might this conversion be done?
Avatar of bdreed35
bdreed35
Flag of United States of America image
Write it this way:

totext(minimum({?DateRange}),"MM/DD/YYYY") & " to " & totext(maximum({?DateRange}),"MM/DD/YYYY")
Avatar of JamesJMcDonnell
JamesJMcDonnell
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

It turned out that the format of the date range was affected by settings on the server I was running the report on. Applying your formula instead of the standard one then caused it to come out as 06/DD/YYYY to 06/DD/YYYY !?
Thanks for your help