How to convert date from text to date format

[Mike Matthews]

Hi:

I have imported data from an old HyperCard file into FM Pro Advanced v8.5.

Data in one of the fields is for date and another is for time. Both were imported as text fields.

Data in the Time field appears like so (for example) 12:10 PM. I changed this field's type from text to time. Viewing all the data as a table and clicking the column header to sort seems to work fine.

Data in the Date field appears like so (for example): Friday, March 16, 2007.

What I want to do is convert this exact data (that is keep the date as--in the example above--Friday, March 17, 2007), but convert the field type from text to date to allow for easy sorting.

I suspect this requires some scripting and/or calculation. I'm a newbie and could use the help.

TIA,

Mike

[Raybaudi]

Hi

let say that the name of your date field ( coming from HyperCard ) is:

HCtextDate

A possible calc to obtain a real date is:

Let([

mName = MiddleWords ( HCtextDate ; 2 ; 1 );

mNumber =

Case(

mName = "January";1;

mName = "February";2;

mName = "March";3;

mName = "April";4;

mName = "May";5;

mName = "June";6;

mName = "July";7;

mName = "August";8;

mName = "September";9;

mName = "October";10;

mName = "November";11;

mName = "December";12

)];

Date ( mNumber ; MiddleWords ( HCtextDate ; 3 ; 1 ) ; RightWords ( HCtextDate ; 1 ))

)

Another possible calc is:

Let([

m = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( MiddleWords ( HCtextDate ; 2 ; 1 ) ; 3 ) ; 1 ; 1 ) / 3 ) ;

d = MiddleWords ( HCtextDate ; 3 ; 1 );

y = RightWords ( HCtextDate ; 1 )

];

Date ( m ; d ; y )

)

[Mike Matthews]

Thanks! Worked great!

Mike