3 Replies Latest reply on May 14, 2011 9:50 AM by LaRetta_1

    How to import dates as same format?

    brett_

      Title

      How to import dates as same format?

      Post

      I have a date column with the format dd/mm/yyyy.  I'd like to import a file that has dates in the format

      Jun 9, 2010

      Once these dates import, they do not format like my other dates.  They stay as the above date.  They also do not sort like a date.  They remain near the top of sorting and out of order.  I can't search on them either.  In the text file I'm importing, the date looks like this

      "Jun 9, 2010"

      I opened the File in Numbers and changed the date format to 6/9/10.  Looking at the file in a text editor, the date doesn't have any quotes.  I was able to import the file without issue.  I have a lot of files to import.  Changing date formats in all of these files isn't feasible.

      Any ideas what is wrong and how I can fix it?

        • 1. Re: How to import dates as same format?
          LaRetta_1

          Hi Brett,

          It sounds like you might have two issues but I still am not entirely clear: 1) dates to be imported are US setting (mm/dd/yyyy) and you want International (dd/mm/yyyy) and your file was created under International and 2) the incoming dates MIGHT be text.  Before I offer suggestions can we get clarity on:

          1) The text file appears to be csv and that is why it is quoted.  When you import into a FileMaker text field (this is a test) what is the result?

          2) If the date is text then we can import into a text field in FM and then set your date field using a calculation after you have imported all data.  If the field is a true date (but US setting instead of International) then we can solve that as well.

          So please verify whether the results when you import into text field and verify which date format you want to end up with (the OS setting your file was created under).

          • 2. Re: How to import dates as same format?
            brett_

            Thanks.  I had some issue like this before and had to call tech support to fix it.  FM is falls flat in this area.  My Macbook date was not in a format FM liked.  After changing that and doing a lot of other stuff in FM, dates started working.

            Wouldn't be easier for me to just change the dates to the format dd/mm/yyyy since that seems to work.  So much quicker and simpler than getting wrapped up in FM's date craziness.

            • 3. Re: How to import dates as same format?
              LaRetta_1

              "Wouldn't be easier for me to just change the dates to the format dd/mm/yyyy since that seems to work."

              I could not say what would be easier because I don't know how many imports you have, whether this is a one-time data migration or an on-going process, what various OS settings you use, how many FM files which were created under the two different date formats you are working with etc.

              In all fairness to FM, it is not "FM's date craziness" but rather inconsistency between operating systems, OS date/time settings and so forth.  I am glad you got it worked out, Brett. :^)