Jump to content

change text to dates


This topic is 6089 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

This topic is 6089 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.