Convert text date value to date string in Crystal Reports


I am using Crystal Reports 2008 with an Excel spreadsheet as my Data Source.

I have a column in my spreadsheet which is set a General (all values in this column are stored as text).

Within Crystal reports this field has been classified as a string field.

The values in this column are displayed as YYYYMMDD (example: 20150323), I would like this field to be displayed as DD-MMM-YYYY (example: 23-Mar-2015 ) in CR.

I'm guessing this would require a formula, please provide step by step on how I can accomplish this.

Kind Regards,
KevinInformation TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try this formula

Local StringVar strDate := {YourStringDateField};
Local StringVar strYear;
Local StringVar strMonth;
Local StringVar strDay;

strYear := Left(strDate,4);
strMonth := Mid(strDate,5,2);
strDay := Right(strDate,2);

strDay + '-' + MonthName(Val(strMonth), True) + '-' + strYear

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinInformation TechnologyAuthor Commented:
Thank you.
FWIW, you could also use a formula like this:

Date (Picture ({your field}, "xxxx/xx/xx"))

 That just inserts "/"s in your date string, and then converts that string to a date.  Then you can use any of the regular formatting options to format the date however you like.

 Of course that relies on your string always being in the YYYYMMDD format (so does mlmcc's solution, but it's not as critical there).  If the field could be blank, or the format could vary (eg. YYMMDD), you'd need to add some checks for that.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.