• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

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,
1 Solution
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

KevinAuthor 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now