How To: Convert String to Date field with multiple Dates 1

[RRAnthon]

Hi group,
I've got a data field that is outputted as a date range:
200410-200506 which indicates Oct 2004 to Jun 2006.

The customer now wants to see the literal dates above. One part of the report is done in code, and I figured it out. However, how do I do this in the CR data field? IOW, change 200410-200506 to Oct 2004-Jun 2005?

TIA.

 

[Turkbear]

Hi,
Look in the help files for details on using the InStr and Left (or Right) functions and use them to parse out the 2 date parts...then use the Date/String and concatenation functions to display the results the way your customer wants..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dgillz]

Left({YourField},Instr({YourField},"-")-1) will give you the first year/month.

Mid({YourField},Instr({YourField},"-")+1) will give you the 2nd year/month.

Create 2 more formulas to covert both of these formulas to numbers, multiply by 100 and add 1:

val({@formula1})*100+1
val({@formula2})*100+1

This will return 2 8 digit whole numbers:
20041001
20050601

Finally, convert these to dates with 2 more formulas:
NumberToDate({@YourFormula1})
NumberToDate({@YourFormula2})

You did not mention your version of crystal, which should ALWAYS be posted. So NumberToDate() may not be available as a formula. However it is available as a download from the Business Objects website.

Or you can do all of this in one big long winded formula:

Numbertodate(Val(Left({YourField},Instr({YourField},"-")-1))*100+1)

From here, I take it you know how to get the results you need.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[RRAnthon]

Thanks turkbear and dgillz for the expeditious responses. Sorry for leaving out the version, CR9. I will plug in that code and see what I can come up with, thanks again!

 

[RRAnthon]

Ok, some of this worked, some didn't, but here's what I have so far:
left({SPName;1.DateRange},instr({SPName;1.DateRange},"-")-1)
gets me 200410.

right({@sFirstDateRange},2) & "/" & left({@sFirstDateRange},4) gets me 10/2004 and then cdate({@sFirstDateRangeConverted}) gets me 10/1/2004. How do I get to Oct 2004 (as for being a complete noob, it took me a while to figure out what/how to create/use a formula)?

 

[dgillz]

If you already have 10/1/2004, just format the date to display as desired. There is no need to modify the underlying data.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz