Date Formatting Problem

[bb84604]

I have some data imported which has the date formatted so FMP apparently can't recognize it.

The dates are three letter months, space, two digit date, comma, space 4 digit year: Like This-> Oct 20, 2009

I tried reformatting with the Format Date but it gives me a question mark. I also tried a "getAsDate" calculation which didn't work either. Ideas??

Thanks Much

[LaRetta]

I'm unsure what you now have in the field. If it is a date field [color:green](or a text field) which was imported with improper date as you indicated (and you haven't changed the format in an attempt to repair it), then you can use this calculation:

Let ( [

m = Int ( 1 + Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( textDate ; 3 ) ; 1 ; 1 ) / 3 ) ;

d = MiddleWords ( textDate ; 2 ; 1 ) ;

y = Right ( textDate ; 4 )

] ;

Date ( m ; d ; y )

)

Before you use a Loop and Set Field (or Replace Field Contents) to repair the field, back up first and you might also create a calculation (result is date) with this and view your results so you won't have any surprises.

UPDATE: This calc will work whether your field is date format or text. But it, of course, should be a date field. You can then display the date any way you wish using layout-level date formatting ( Format > Date ).

Edited November 7, 2009 by Guest
Added update & green

[bb84604]

You are awesome.

Thanks so much.