Selecting a Date from a String

[cheeseman10]

Hi all,
I have a string (lot number) that is unique for each item we produce. One constant is the date but it can vary in it's location. I would like to trim out the extra numbers before and after and leave just the date.

example data below.

1111111222222333 (this is the string some of the time)
1111112222223333 (this is the string the rest of the time)

I need a formula to only show the 2's.
Also if you can explain how the formula extracts the 2's that would be great. I have read some threads that are similar but I cannot understand how the formulas work.

Thanks!

 

[lbass]

What is the date in the sample strings? I see nothing that looks like a date. How do the 2's relate to the date?

-LB

 

[cheeseman10]

I put the ones as a sample as follows.
1 = six or seven digits depending on the order (batches = 6 digit work orders = 7)
2 = six digit date that i want to single out
3 = 3 or 4 digits depending on the order

sample data

2184796012511003

 

[lbass]

Okay, so the two's are showing the desired position. I guess the logic would be:

stringvar x;
if {table.order} = "batch" then
x := mid({table.string},7,6) else //start 7th position from left, for 6 characters total
if {table.order} = "work order" then
x := mid({table.string},6,6);//start 6th position from left, for 6 characters total

To convert to a date datatype, add this clause to the formula above at the end:

date(2000+val(right(x,2)),val(left(x,2)),val(mid(x,3,2)));

-LB

 

[cheeseman10]

LBass,
First of thanks for the response.
I used your formula and I was returning some incorrect data. I was getting the wrong 6 digits.
When I changed your formula to the one below it seems to be working. Do you know why this is?
Also when I add the "date(2000+val..." ect ect string I get the message below.

"A day number must be between 1 and the number of days in the month."

Again thanks for all the help!

 

[cheeseman10]

edit* I changed the 7,6 to 8,6 and 6,6 to 7,6

if {table.order} = "batch" then
x := mid({table.string},8,6) else
{table.order} = "work order" then
x := mid({table.string},7,6);

 

[lbass]

I just believed you when you said 6 or 7. Do you have this working now?

-LB

 

[cheeseman10]

Yes it is working thanks!