converting a date

[janet K]

I have a file that I am trying to import into filemaker.

The date is set up as follows:

May 1, 2009

How do I convert those dates to something that filemaker recognizes as a date. I want to be able to sort and find on all these dates. Right now I am importing them into a field that is defined as a date but it displays a question mark.

I feel like this is one of those things that should be so simple, but I cant find any solutions. Do I have to write a custom function for this?

[Raybaudi]

No need of a custom function...

Only this calc:

Let([

m = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( yourField ; 3 ) ; 1 ; 1 ) /3 ) ;

d = MiddleWords ( yourField; 2 ; 1 );

y = RightWords ( yourField ; 1 )

];

Date ( m ; d ; y )

)

( yourField is the TEXT field where you stored the imported date )

[janet K]

sorry may was a bad example. It is not a 3 character month is is the whole word spelled out so the length will vary.

The day also changes because they are giving me one character days for 1-9.

I will look more closely at what you sent me and see if I can modify it.

[Raybaudi]

It's not important if there is the whole name, the calculation will work the same way.

Do not modify the calc !

Edit: the one important thing is that there is a space between each value.

[janet K]

Thanks, that worked