change text to dates

[tom24569]

I've imported a bunch of records that all have a text field to display a date, in the format "April 3, 2005". Is there a way to change these text fields to working date fields?

[LaRetta]

To change them to working date fields, they must BE date fields. If you simply change them to date field as they stand, they will be invalid dates and if left as text (even if they look like true dates ##/##/####) then FileMaker can't evaluate them as dates. So you must make them dates. The safest way is to:

1) Back up first

2) Create a new Date field called newDateField

3) Run Replace Field Contents through newDateField with calculated result of:

[color:blue]Let ( [

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

d = MiddleWords ( textdate ; 2 ; 1 ) ;

y = RightWords ( textdate ; 1 )

] ;

Date ( m ; d ; y )

)

4) After verifying it worked and if you truly want to preserve the old textdate field (ie, if you have it used throughout your solution and don't want to find it and respecify it as the new field), then now change it to a date. Then run Replace Field Contents through it, specifying by calculation and only put: [color:blue]newDateField

5) Then you can delete the newDateField.

... but be sure it is correct at each step; it's easy to make a mistake. NOTE: This following sentence in green is not quite what I meant. See below for clarification. [color:green]You could instead just convert your textDate to proper date and THEN run Replace Field Contents[] on it. But if anything goes wrong ... better to handle it in two steps so you can check yourself. Also note that, if any of the text dates are improper, ie, you have February 29, 2003 (which is NOT truly a leap year), then FileMaker will create the date as 3/1/2003.

[tom24569]

Laretta - thanks, that worked beautifully!

[LaRetta]

You could instead just convert your textDate to proper date and THEN run Replace Field Contents[] on it.

Error ... I meant the reverse of course. You can't make it a date and then run the calc through it because FM won't recognize it as a date and it'll break on the conversion. I meant run the calc thru it and then change it to a date. I hate it when I mis-talk. But you got it using my first suggestion anyway. :smirk: