6 Replies Latest reply on Apr 5, 2012 6:12 PM by MicheleOlson

    Converting text fields to date in pro 11

    martinransom

      I am a newbie to Filemaker just imported about 1300 records into a new database several date fields where in a text format I need to convert to date, but not sure how to do this. The format of the field is 12 Aug 2010 for example

        • 1. Re: Converting text fields to date in pro 11
          MicheleOlson

          Could you give more information about the data?

           

          What does it look like in the text field?

           

          Mar 30 2011

          3/30/11

          etc.

          • 2. Re: Converting text fields to date in pro 11
            martinransom

            Hi Michelle

             

            Thanks for coming back to me the format is 12 Aug 2010 it was a date in the old database I imported the data from but it has importedit as a text field.

             

            Hope you can assist

            • 3. Re: Converting text fields to date in pro 11
              MicheleOlson

              Ok.

               

              You say it was a date in the old database. What application created the old database?

              • 4. Re: Converting text fields to date in pro 11
                wrwaugh

                If your text date has spaces between the date teh month and the year as you show in your post, then you can use a calculation using the Date ( month ; day ; year ) fundction with  LeftWords MiddleWords, and RightWords to complete the Month;Day;Year part of the Date ( month ; day ; year ) function.

                 

                so where your original date is a field called TextDate, a calculated date result would be:  Date (MiddleWords (TextDate,2,1) ; LeftWords(TextDate, 1); RightWords(TextDate, 1)

                 

                Now you have the problem that the month is an abbreviated text not a number so you will need to make it a number.  A Case statement will do:

                (Case(MiddleWords ( TextDate ; 2 ; 1 ) ="Jan" ;"1"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Feb" ;"2"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Mar" ;"3"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Apr" ;"4"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="May" ;"5"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Jun" ;"6" ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Jul" ;"7"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Aug" ;"8"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Sep" ;"9"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Oct" ;"10"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Dec" ;"12"  ;"")

                 

                so your final calculation will be:

                 

                Date (

                (Case(MiddleWords ( TextDate ; 2 ; 1 ) ="Jan" ;"1"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Feb" ;"2"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Mar" ;"3"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Apr" ;"4"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="May" ;"5"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Jun" ;"6" ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Jul" ;"7"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Aug" ;"8"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Sep" ;"9"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Oct" ;"10"  ;

                MiddleWords ( TextDate ; 2 ; 1 ) ="Dec" ;"12"  ;""));

                LeftWords (TextDate;1);

                RightWords (TextDate;1))

                 

                 

                This is rather cumbersome, and there are probably very nice custom functions created to do this, but since you are new to Filemaker I thought you might like to see a thought process using a calculation.  Note that my calculation converts your TextDate to Amercan date format.  If you are using European Date format you will need to rearrange the calculation.

                 

                You can also use a SetField script to set a date field from the TextDate field where you would use that same calculation.

                 

                On the forum here, someone will surely give you a nice simple function to use....

                • 5. Re: Converting text fields to date in pro 11
                  ch0c0halic

                  Martin may be texting his dates,

                   

                  The format is: Day Month Year

                  12 Aug 2010

                   

                  The math is pretty easy.

                  Take the first word as a number for the Day

                  Take the actual text part to determine what month it is.

                  Take the last word as a number as the year.

                  You want FileMaker to do all the work so use the Date ( Month ; Day ; Year) function to return the a date regardless of the format used on the computer.

                   

                  To get the text part is the only hard part because there are several ways to do that.

                  Filter ( MyDate ; "abcd..." ) - where only alpha characters are left.

                  MiddleWords ( MyDate ; 2 ; 1 ) - easiest

                  Subsitute ( MyDate ;

                  [ " " ; "" ] ;

                  [ "0" ; "" ] ;

                  [ "1" ; "" ] ;

                  ...

                  ) -  pretty messy and basically does the same thing as the filter but removes instead of keeps values.

                   

                  I'm going to use the easy one §^=)

                   

                  Date ( Position ( "xxJanFebMarAprMayJunJulAugSepOctNovDec" ; MiddleWords ( MyDate ; 2 ; 1 ) ; 1 ; 1 ) / 3  ; LeftWords ( MyDate ; 1 ; ) ; RightWords ( MyDate ; 1 ) )

                   

                  The way the month selection works is the source string has every three characters is a single month number in the correct order. So the position of the month in the string divided by 3 gives the actual month.

                  • 6. Re: Converting text fields to date in pro 11
                    MicheleOlson

                    Martin,

                     

                    You've gotten two good ideas on how to turn the text date data into real date data.

                     

                    The reason I asked what the old database application was is because I have found that sometimes opening the data file in Excel and defining the incoming field as a date field will do the trick. Save the Excel file, then open with FM and FM will recognize the date as a date.

                     

                    Just a thought....