4 Replies Latest reply on Jun 13, 2012 4:03 PM by aidren

    text to date conversion

      Title

      text to date conversion

      Post

      I am having a problem converting an imported date which is text in this format: 02-Dec-2011

      I would like it converted to a date field that reads 02/12/2011

      Can someone help me with this? I found some information for a calculation at Filemaker site, but what I found I can't seem to get to work.

       

      Thanks

       

        • 1. Re: text to date conversion
          philmodjunk

          Try this:

          Let ( [ t = YourTextField ;
                    d = Left ( t ; 2 ) ;
                    m = ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Middle ( t ; 4 ; 3 ) ; 1 ; 1 ) / 3 )
                    y = Right ( t ; 4 ) ];
                    date ( m ; d ; y ) // date must have the values in this order even if you want to display it in a different format such as DD/MM/YYYY
                )

          Note: Dates are stored internally as an integer counting the days from 31/12/0000 to the date shown in the field. To see dates displayed in DD/MM/YYYY format, you can specify that date format on the data tab in the inspector if the systems location settings do not display it in this format automatically.

          • 2. Re: text to date conversion

            Try this:

            Let ( [ t = YourTextField ;
                      d = Left ( t ; 2 ) ;
                      m = ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Middle ( t ; 4 ; 3 ) ; 1 ; 1 ) / 3 )
                      y = Right ( t ; 4 ) ];
                      date ( m ; d ; y ) // date must have the values in this order even if you want to display it in a different format such as DD/MM/YYYY
                  )

            Note: Dates are stored internally as an integer counting the days from 31/12/0000 to the date shown in the field. To see dates displayed in DD/MM/YYYY format, you can specify that date format on the data tab in the inspector if the systems location settings do not display it in this format automatically.

             

            Thank you. I tried this and got a dialogue saying it could not find text field for 'y', so I created text fields for d, m, and y. Then the dialogue read

             

            "A number, text constant, field name or "(" is expected here."

             

            I was highlighting the y.

            • 3. Re: text to date conversion
              philmodjunk

              I left out a ;

              Make it:

              Let ( [ t = YourTextField ;
                        d = Left ( t ; 2 ) ;
                        m = ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Middle ( t ; 4 ; 3 ) ; 1 ; 1 ) / 3 ) ;
                        y = Right ( t ; 4 ) ];
                        date ( m ; d ; y ) // date must have the values in this order even if you want to display it in a different format such as DD/MM/YYYY
                    )

              • 4. Re: text to date conversion

                Thank you -- thank you -- thank you!!Smile

                 

                I was almost there with the other information I had found, but, I have to say, your solution is so much more elegant.

                 

                Thanks again, Phil