Date formatting

[Snow-i]

So I have a "last updated" field for my customer records. It is a manual enter date field with a drop down calender. The only problem is that the program that I imported this field from uses an unusual date format, ex: (01-Feb-11), so most of the records are using that format. I'd like to get all of the records onto the standard (dd/mm/yyyy) that filemaker uses. Is there an easy way to do this?

Thanks in advance!

[comment]

For Filemaker, "01-Feb-11" is a meaningless text string. You need to translate the data to a valid Filemaker date. First, import the data into a text field. Then you can replace the contents of your date field with a calculated result =

Date ( 

Let ( [

str = "janfebmaraprmayjunjulaugsepoctnovdec" ;

mmm = MiddleWords ( ImportedText ; 2 ; 1 ) 

] ;

Ceiling ( Position ( str ; mmm ; 1 ; 1 ) / 3 ) 

) ;

LeftWords ( ImportedText ; 1 ) ;

2000 + RightWords ( ImportedText ; 1 ) 

) 

(assuming all your dates are in 2000 or later).

[Vaughan]

...the program that I imported this field from uses an unusual date format, ex: (01-Feb-11)

That's a standard date format for much of the civilised world.

Not everybody uses the same paper size, calls weekdays and months the same names and uses the same format for calendar dates. In fact the same FMP file can uses different date formats when opened on different computers, based on the OS preferences.

I sometimes do a check for date format with this simple calc:

Substitute( Date( 11 ; 22 ; 3333 ) ; [ 1 ; "M" ] ; [ 2 ; "D" ] ; [ 3 ; "Y" ] )