Importing Month/Year Date

[charlesoutcalt]

Hello. I've got what I hope is a quick question. I am importing records from a comma-delimited file. The file contains dates in two formats.

The first format, which is like this:

01/05/04

imports just fine, as: 01/05/2004.

However, the second format, which is like this:

01/05 (for January 2005)

imports in a screwy way, as FM reads it as May 1, which is definitely not correct.

I can import this date as text, but I will need to be able to sort these fields as dates.

What can I do?

THANKS!

Edited December 7, 2006 by Guest

[LaRetta]

...can import this date as text, but I will need to be able to sort these fields as dates.

If you are importing into a date field, 01/05 should be interpreted as January 5, 2006 (current year). How will you determine what year is appropriate for the record? If you are getting a reverse month/year then it means that the file is set to international and is incorrectly reading the text. If so, you should clone the fine under your current setting and it'll stick.

But you should certainly make them true dates. Don't leave them as text because it will haunt you. :wink2:

[charlesoutcalt]

Thanks for the quick response. I don't think I was clear, though:

In my file, 01/05 means January 2005.

After import, the date is May 1, 2006.

So, things are really wrong after import.

How can I convince FM to import my date as month and year?

[LaRetta]

Ah. Then add an auto-enter via calculation to your new date field. How will determine what day to insert? And will the month and year always be padded with leading zero?

[charlesoutcalt]

I _think_ I understand. . .

Are you suggesting that I import that field as text, then modify it to add a day of month, then convert it to date format?

If so, how can I do that? I'm very much a newbie here.

If my field reads like this: 05/05 how can I make it read like this: 05/01/05?

By the way, I don't care what day of the month I add, as that doesn't matter for my calculations. In addition, yes, I always have leading zeroes.

Thanks!

[charlesoutcalt]

Sadly, I am still stumped on this.

As I understand it, I need to convert dates from this format:

month/year (as in 01/05, for January 2005)

to this format:

month/date/year (as in 01/02/05, for January 2, 2005).

Can I convert these dates while they are in text format via a calculation?

I don't care which day of the month I use--that isn't a concern for me, as I will never sort or search by anything more specific than month and year--but I do need to do something, as these month/date format years are consistently imported wrong. . .

THANKS!

[LaRetta]

Back up first. FM, as a date, doesn't like switching its pieces around during import so leave your textDate field as text (with NO auto-enter) and create a new calculation (result is date)

Left ( textDate ; 3 ) & 1 & Right ( textDate ; 3 )

Then search on this new calc instead.

LaRetta

[charlesoutcalt]

This worked beautifully. Thank you! You make it seem so easy. . .