3 Replies Latest reply on Aug 8, 2012 8:44 AM by Kaps_1

    Importing date strings

    Kaps_1

      Title

      Importing date strings

      Post

      Hi,

      I am trying to manipulate some "date strings" that I have imported from Excel to Filemaker 12. The strings are imported into a field called "Imported Date" - which is a text string.

       

      An example string is "Thursday, December 15, 2011" - ignoring the quotes. This is captured as a text field in Filemaker. I want to convert this to a date value such as Dec-15-2011. I suspect that the answer lies in the use of the subsitute function.

      However, I cant see how. Any suggestions ? thanks

       

      Kaps

        • 1. Re: Importing date strings
          philmodjunk

          What you'll need is the date or getasdate function to convert the data into a date data type.

          Let ( [ Dt = YourTable::YourDateTextField ;
                    Mn = MiddleWords ( Dt ; 2 ; 1 ) ;
                    M = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( Mn ; 3 ) ; 1 ; 1 ) / 3 ) ;
                    D = MiddleWords ( Dt ; 3 ; 1 ) ;
                    Y = RightWords ( Dt ; 1 ) ];
                    Date ( M ; D ; Y )
                 )

          Use this calculation with either a field of type date where this is an auto-enter calculation or in a calculation field that returns date as its return type.

          • 2. Re: Importing date strings
            Kaps_1

            Thanks. I cant quite seem to get this to work.

            I always get 01/01/0001 as the result.

            I have broken down M, D and Y into their constituent parts and these all evaluate correctly.

            M, D and Y are all defined as numbers.

            The resulting date field is defined as a date. any suggestions ?

             

            thanks

             

            Kaps

            • 3. Re: Importing date strings
              Kaps_1

              Have found the bug. Field was being reset. sorry !!