Parse date info

[nightdesigns]

I need to parse a date from a field. Unfortunately, the way it's entered is not consistent. It's always in MDY format, but somtimes it's M/DD/YY or MM/D/YYYY or MM-DD-YY, etc.

What would be the best way to parse all of this data?

This is on FM5

Thanks

[Lee Smith]

Try this modified calcalulation that was shared by Bob Weaver.

Let([

Parts=Substitute(

YourDateField; ["/";" "]; ["-" ; " "]);

Month=LeftWords(Parts;1);

Day=MiddleWords(Parts;2;1);

Year=RightWords(Parts;1)];

Day &"/"& Month &"/"& Year)

HTH

Lee

[Raybaudi]

Hi

... but those dates are all inside the same date field ?

or is that field a text field ?

[LaRetta]

No Let[] within vs. 5 ... drat! We are so spoiled with 7/8/8.5!

Edited March 9, 2007 by Guest

[Lee Smith]

:bang:

Sorry, I missed the version.

Lee

[Raybaudi]

This is the same calc of Lee, but for v. 5

Date (

LeftWords ( Substitute ( Substitute ( YourField , "/" , " " ) , "-" , " " ) , 1 ) ,

MiddleWords ( Substitute ( Substitute ( YourField , "/" , " " ) , "-" , " " ) , 2 , 1 ) ,

If(

Length( RightWords ( Substitute ( Substitute ( YourField , "/" , " " ) , "-" , " " ) , 1 ) ) = 2 ,

"20" & RightWords ( Substitute ( Substitute ( YourField , "/" , " " ) , "-" , " " ) , 1 ) ,

RightWords ( Substitute ( Substitute ( YourField , "/" , " " ) , "-" , " " ) , 1 )

)

)