Jump to content

Importing Month/Year Date


charlesoutcalt
 Share

This topic is 5778 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
Link to comment
Share on other sites

...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:

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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. :wink2:

LaRetta

Link to comment
Share on other sites

This topic is 5778 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.