Extract date from a large text field

[GCL2007]

I'm trying to extract a date that was entered into a large text field. Date could be of format MM/DD/YY or M/D/YY or M/DD/YY or MM/D/YY and may appear anywhere within the text field. Is there a way to create a formula in Crystal XI to extract just the date?

 

[goldfishhh]

If you knew where in the big mess of text your date lived, you could use MID() and Dateserial() and Month(), day(), year.


dateserial(year(mid({field.bigtextmess},5,2)),month(mid({field.bigtextmess},10,2)),day(mid({field.bigtextmess},14,2)))

D-

 

[GCL2007]

Thanks -Unfortunately, the date field is not in the exact spot in the field on each record.. I think I'll need something that searches for the / then backs off one or two characters to get the month digit, then gets the rest of the string.. no idea how to do it...

 

[lbass]

If dates are the ONLY components of the string that contain slashes, then you could use a formula like this:

stringvar array x := split({table.string}," ");
numbervar i;
numbervar j := ubound(x);
datevar y;
for i := 1 to j do (
if instr(x,"/") <> 0 then
y := date(x
);
y

If the date is adjacent to a punctuation mark you would have to replace that with a blank for this to work.

-LB

 

[Turkbear]

Hi,
The Instr function will let you find the position of the / characters, but how will you know how many chars you need....To get an interim result, you can use a formula with Instr and Mid to find the first / then try getting 2 before and 7 after to see what format most dates are in.

Try:

@GetDatemaybe

Mid({Table.BigString},Instr({Table.BigString},"/") -2,10)






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

 

[GCL2007]

Thanks All,
LB - I'm trying to follow your formula. If I cut and paste into Crystal formula and change the table.field name to my table, I get a "The ) is missing" error. This formula is way beyond my ability.. Just wondering if you could help where I'm missing the ).

If I put the ) at the end of your statement after the y(just guessing it goes there), I get a "Bad Date Format String" with the date(x
) high-lighted. Just wondering if you had any thoughts..

stringvar array x := split({table.string}," ");
numbervar i;
numbervar j := ubound(x);
datevar y;
for i := 1 to j do (
if instr(x,"/") <> 0 then
y := date(x
);
y


Thanks again

 

[lbass]

You should show me YOUR version of my formula.

You would get the bad date format string if your date is next to a punctuation mark. So you could change it to something like this:

stringvar array x := split({table.string}," ");
numbervar i;
numbervar j := ubound(x);
datevar y := date(0,0,0); // added this
for i := 1 to j do (
if instr(x,"/") <> 0 then
y := date(replace(replace(replace(x,".",""),",",""),";",""))
);
y

-LB

 

[GCL2007]

Thanks once again LB.
It appears the record I was getting the error on had multiple date fields. I think the majority of dates that I want to extract have a : at the end of the date in the text field. (for example, 6/23/09
Sorry to be a pain, and I really appreciate this, but is there a way to modify the formula to only extract the dates with a colon immediately after the date?

 

[lbass]

stringvar array x := split({table.string}," ");
numbervar i;
numbervar j := ubound(x);
datevar y := date(0,0,0);
for i := 1 to j do (
if instr(x,"/") <> 0 and
instr(x,":") <> 0 then //check for / AND :
y := date(replace(replace(replace(replace(x,".",""),",",""),";",""),":")) //added colon
);
y

The multiple dates wouldn't cause the problem, the colon did. Note that this only returns one value, so if you had more than one date with a colon then it would return the last one in the string.

-LB