I would import the date to a normal Text field and then from that field do a Calculated field that transform the data into your wished format with following formula
field = YourTextField;
day = Middle ( field ; Position ( field ; "/" ; 1 ; 1 )+1 ; Position ( field ; "/" ; 1 ; 2 ) - Position ( field ; "/" ; 1 ; 1 ) );
month = Left( field; Position ( field ; "/" ; 1 ; 1 )-1 );
year = right( field ; 4 )
Date ( month ; day ; year )
If this is only an occasional import and does not have to be scripted, you could try setting your Mac OS "Language & Text" format to "United States" just before doing the import. Set it back to "United Kingdom" after the import.
FileMaker Pro should respond immediately to the system format change.
Or you can do as Johan suggests and rearrange the date yourself within FileMaker after importing. If you do this frequently the advantage of this is that you can set it up with scripting to automatically handle imports of this type.
For some variety, an alternative formula to do the rearranging in a text field is
theDate = Substitute(YourTextField;"/";" ");
YYYY = MiddleWords(theDate; 3;1);
MM = MiddleWords(theDate; 1;1);
DD = MiddleWords(theDate; 2;1)
Johan's approach is the one I typically use too.
Oddly, and confusingly, FMP also has date inconsistencies with dates passed back from ExecuteSQ vs its regular dates. Thus, you can't do regular date math with a FMP Date and an ExecuteSQL date until you change one of the two dates.
So, until FMI can unify date formats somewhat (at least, please!), it's good to have a couple Custom Functions handy for date issues.
I'm a bit of a newbie and using the calculated script is a wee bit above my pay grade. Nevertheless this is the encouragement I need to help me expand my limited FM skill level.
Hi Tom thanks for this. I just did a quick test on changing the regional settings before (USA) an after import (UK) and it works!!! I normally only import at month ends but I will attempt the calculated scripting. Best Neil
Thanks John but I do use the Training Series and also lynda training site. Guess I'm just a little slow in grasping the technicalities. Best Neil