6 Replies Latest reply on Aug 24, 2016 6:46 AM by beverly

    Imported date

    FeldyNYC

      I imported some data and one field was a date. It was imported as 20160630. How would I format this to make it into a date? Do I need to create a new field? Is there a formula?

       

      thanks.

        • 1. Re: Imported date
          Johan Hedman

          FileMaker stores date differently depending what local date format you use. For me I use YYYY-MM-DD, but in other countries there are other ways like MM/DD/YYYY for example. You need to have the date in your file that you import as the same format as you have in your FileMaker database.

          • 2. Re: Imported date
            FeldyNYC

            Thank you. Its already imported, but as a string of numbers. Any way to convert it into a date when already in FileMaker?  Kind regards.

            • 3. Re: Imported date
              Johan Hedman

              Then you can use the function Replace and do a Calculation where you do

               

              Let(

              date = YourDateField

              ;

              // Date( Month ; Day ; Year )

              Date( Middle( date ; 5 ; 2 ) ; Right( date ; 2 ) ; Left( date ; 4 ) )

              )

               

              And that will calculate the numeric numbers into date format

              • 4. Re: Imported date
                alangodfrey

                Note that Johan is referring to the Records Menu command to 'Replace Field Contents' (not the Find/Replace function in the Edit menu).

                 

                Take a backup - there is no 'Undo' function!

                 

                You could test your formula first by Finding one record and Replace Field Contents for it.

                • 5. Re: Imported date
                  David Moyer

                  Hi,

                  I wanted to add that it sounds like you might be working with a text field.

                  If that's the case, you should consider using a date field instead.

                  • 6. Re: Imported date
                    beverly

                    "string" (of numbers) is the optimum word here. As Johan shows, you can "parse" text (even if 'numbers').

                     

                    I've seen imported "dates" (_as_ numbers) not convert so easily. FM uses "1/1/0001" as Day One (the number "1"). Excel uses "1/1/1900" as Day One (the number "1").

                     

                    Using any of the parsing, you can auto-enter a calc to test if IsValid(), then leave as is, otherwise convert via parsing. But otherwise "cleaning data after import" may also be necessary. And heed Alan's advice:

                    backup. I might even have a temporary field set with this kind of cleaned data to verify that I haven't used the wrong calc!

                     

                    beverly

                    1 of 1 people found this helpful