Date Formats

[Mark Appleby]

Who can help me? I have to import a .CSV file which has a date on each record. The Date is in a US format but I have to change it to a European format ie "01/01/2005" the format could be "1/1/05 or 12/12/05". I was thinking of changing by saying Left charachters but I can not do this as there are sometimes one number for date sometimes two there are no leading zeros. How do I work around this problem?

I tried just telling the system to change it in the format screen but I have found it isn't wirking correctly in all cases pprobably because it is in text format.

Is there a way to extract the numbers before the first "/" between the two "/ /" and following the last "/"?

Mark

[RalphL]

You can try the following:

Month = Left ( Date; Postion (Date; "/"; 1; 1) - 1)

Day = Middle (Date; Postion (Date; "/"; 1; 1) + 1, Postion (Date; "/"; 1; 2) - Postion (Date; "/"; 1; 1))

Year = Right (Date, 4)

This assuumes date is a text field and 4 diget year.

I haven't tested this so you may have adjust the constants.

[Lee Smith]

I would change the data to match my needs in a Text Editor before I import it.

Not not sure I understand all of the particulars, as you started out showing a 4 digit year, but gave to examples of 2 digit years, and I think you want to insert a 0 in both the month and day where there is now a single digit. Also, the following may need to be adjusted for somethings like if the date is enclosed in "" with no spaces or with spaces in front of it. Relying on what you posted above however, the patters would look something like this In a text editor using grep patterns find and replace.

Using this pattern, you can replace the two digit 05 with 2005 (if you have other years involved, you need to adjust this).

Find:

(d+/)(d+)(/)(05)

Replace

12/2005

using this pattern to find and insert a 0 before in the first part of the date (current date must be proceeded with a space, if it is enclosed in "" or , let me know and I'll adjust it for you)

Find:

( )(d/)

Replace

02

and this pattern to find and insert a 0 in the part of the date

Find

(dd)(/)(d/)

Replace

1/03

HTH

Lee

[BobWeaver]

You can use the substitute function to change the slash to a space, and then use the word functions to get the separate date parts.

Let([

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

Month=LeftWords(Parts;1);

Day=MiddleWords(Parts;2;1);

Year=RightWords(Parts;1)];

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

[Lee Smith]

Hi Bob,

I like that a lot.

I actually played around with the Let before I suggested my old standby, but couldn't get there from here.