Extracting data from a string 1

[iwm]

I created this report using Crystal Reports 9 several years ago. The description field is a string containing the invoice date and the item description.

The client instructions for formatting from the description field were as follows:
1. (First character) Open parenthesis
2. Followed by mm/dd/yyyy date format (slash will be used to separate the date segments).
3. (Eleventh character) Closed parenthesis
4. Followed by item description

Example: (12/15/2010)office supplies

I used the following formula to create the invoice date field:

If left({ProcItem.description}, 1) = chr(40) then
datevalue(Mid ({ProcItem.description}, 2,10))

And I used the following formula to create the item description field:

If left({ProcItem.description}, 1) = chr(40)
then Mid ({ProcItem.description}, 13)
else {ProcItem.description}

They have recently decided they would like to be able to enter the date using any recognized date format. The updated instructions are as follows:

1. (First character) open parenthesis
2. Followed by any recognized date format (slash will be used to separate the date segments)
3. Closed parenthesis
4. Followed by item description

I need help creating two new formulas, the invoice date formula and the item description formula.

The invoice date field is derived from the data contained in the parenthesis and the item description field is the remaining data. If there are no parenthesis in the string then entire string becomes the item description field.

Your assistance is greatly appreciated.

Ingrid

 

[lbass]

What are examples of 'recognized' date formats? The issue is of course how to interpret something like this: 11/12/10. Or even 11/12/2010. How do you know which recognized date format is being used?

-LB

 

[iwm]

Recognized date formats:
mm/d/yy,
mm/dd/yy,
mm/dd/yyyy.

 

[lbass]

Please do not post the same thread in multiple forums.

So the sequence is always months, days, years? If so, then I think you could do this:

//{@date}:
if left({ProcItem.description}, 1) = chr(40) then
date(extractstring({ProcItem.description},"(",")"))

-LB