Import into european date format

[Toni]

Hi

I am having some dificulties in importing data from a txt file. In this txt file the date is in american format mm/dd/yyyy, while I want to import into a date field in european date format dd/mm/yyyy.

I am not succeding in doing this.

Any idea?

Edited December 16, 2009 by Guest

[comment]

In version 6?

[TheTominator]

I am having some dificulties in importing data from a txt file. In this txt file the date is in american format mm/dd/yyyy, while I want to import into a date field in european date format dd/mm/yyyy.

Import the mm/dd/yyyy data into a text field. Use a Replace field Contents with a calculation to put the date into the real date field.

In the calculation, break up the text into the three pieces and put them back together with the Date() function.

Edited December 16, 2009 by Guest

[Toni]

I managed to do it. The problem was not only the format but sometimes the content of the field itself. In the text file the date was without the leading zeros for example:

4/10/2009 or 11/6/2009

So i used the following calculation:

Date ( GetAsText (Left (Date; Position (Date;"/";1;1))); GetAsText (Middle(Date;(Position (Date;"/";1;1));(Position(Date;"/";4;1))-(Position (Date;"/";1;1)))); GetAsText (Right (Date;4)))

Thank you for your replies, Toni

[TheTominator]

In the text file the date was without the leading zeros for example:

4/10/2009 or 11/6/2009

So i used the following calculation:

Date ( GetAsText (Left (Date; Position (Date;"/";1;1))); GetAsText (Middle(Date;(Position (Date;"/";1;1));(Position(Date;"/";4;1))-(Position (Date;"/";1;1)))); GetAsText (Right (Date;4)))

That type of text handling is suitable for FileMaker version 5 and later (with function names and syntax changed as needed), but it can be simplified in the later versions of FileMaker.

You can now use:

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

Edited December 18, 2009 by Guest

[Toni]

Hmm, very good point TheTominator

Thank you for that

[Lee Smith]

Here is a Let calculation that was posted by Bob Weaver a while back,

Let([

Parts=Substitute(DateField;"/";" ");

Month=LeftWords(Parts;1);

Day=MiddleWords(Parts;2;1);

Year=RightWords(Parts;1)];

Day &"/"& Month &"/"& Year)

HTH

Lee