importing dates in wrong format

[photog]

I'm importing text data from another application. The date fields are in 2005/07/28 format and are importing as such. I need to change the format to standard Filemaker format of 07/28/2005. Help....any ideas? Thx.

[-Queue-]

Create a text field to hold the imported data. Change your date field to be an auto-enter calculation of

Let( T = Substitute( textField; "/"; " " );

Date( MiddleWords( T; 2; 1 ); RightWords( T; 1 ); LeftWords( T; 1 ) ) )

Then import into the text field with auto-enter options enabled.

[photog]

Thx, Queue, but I think I'm doing something wrong as I'm getting "cannot find field" and "don't understand command" and "too many or not enough ((,( or )),)" type replies.

Also, I am importing 7 different date fields and assume that I need to change your code to identify the Text field name and the Date field name of each field, yes?

I think I'm way over my head here. The dates are

importing as yyyy/mm/dd and I "simply" need them as mm/dd/yyyy. I'm a total novice with FileMaker. Thx.

[comment]

Is this a one-of, or are you going to do this periodically?

[photog]

Need to do this once to make sure it will work correctly, then only once more for final database creation.....but I could just do this once if need be. Thx in advance for your ideas.

[comment]

If it is only once or twice, I would do the following:

1. In File Options > Text, set the file either to 'Always use system settings' or 'Ask...'.

2. Close your file.

3. Change your OS setting to Year-Month-Day (in System Preferences > International > Format > Customize > Short Date).

4. Open your file. If asked, select 'Use system formats'.

5. Import your text file. You can now import the dates directly into date fields.

6. Close your file.

7. Change your OS setting back to your preferred format.

8. Open your file. If asked, select 'Use system formats'. In File Options > Text, select whatever (I always try to set it to use current system settings, so the file doesn't beep when someone else opens it).

That's about it.

[-Queue-]

A closing parens was missing from the Let when I copied and pasted it. I fixed the calc. Sorry about that.

[photog]

Thx to all. Finally fixed data by using a calculation for each date field after import:

Date(Middle(Date; 6; 2); Right(Date; 2); Left(Date; 4))

Date(Middle(Date first order; 6; 2); Right(Date first order; 2); Left(Date first order; 4))

etc, etc.. ("Date" and "Date first order" are date field examples)

When attempting to use Sys Pref to solve problem I found that, wierdly, only some of the date data was responding. When I tinkered with Format>date for all date fields (Layout mode) only some data responded as well, occasionally a question mark would appear in some fields because data was not formatted correctly. Or some data appeared incorrectly but was nonetheless not showing a question mark.

In any case the above calculations worked well. Thanks again to all.