7 Replies Latest reply on Nov 3, 2016 5:37 AM by nhughes@iafrica.com

    date format issue

    nhughes@iafrica.com

      I have an iMac and I'm working with data exported from quickbooks. QB dates are exported in mm/day/yyyy. format. I want to convert to day/mm/yyyy format but encountering no success with either Excel (CSV and XLXS) or within Filemaker 14. My region is set to UK. I have googled  endlessly without finding a solution. Anybody able to shed any light on this issue?

        • 1. Re: date format issue
          Johan Hedman

          I would import the date to a normal Text field and then from that field do a Calculated field that transform the data into your wished format with following formula

           

          Let (

          [

          field = YourTextField;

          day = Middle ( field ; Position ( field ; "/" ; 1 ; 1 )+1 ; Position ( field ; "/" ; 1 ; 2 ) - Position ( field ; "/" ; 1 ; 1 )   );

          month = Left( field; Position ( field ; "/" ; 1 ; 1 )-1 );

          year = right( field ; 4 )

          ]

          ;

          Date ( month ; day ; year )

          )

          • 2. Re: date format issue
            TomHays

            If this is only an occasional import and does not have to be scripted, you could try setting your Mac OS "Language & Text" format to "United States" just before doing the import.  Set it back to "United Kingdom" after the import.

            FileMaker Pro should respond immediately to the system format change.

             

            Or you can do as Johan suggests and rearrange the date yourself within FileMaker after importing.  If you do this frequently the advantage of this is that you can set it up with scripting to automatically handle imports of this type.

             

            For some variety, an alternative formula to do the rearranging in a text field is

             

            Let(

            [

            theDate = Substitute(YourTextField;"/";" ");

            YYYY = MiddleWords(theDate; 3;1);

            MM = MiddleWords(theDate; 1;1);

            DD = MiddleWords(theDate; 2;1)

            ];

            Date(MM;DD;YYYY)

            )

             

             

            -Tom

            • 3. Re: date format issue
              fmpdude

              Johan's approach is the one I typically use too.

               

              Oddly, and confusingly, FMP also has date inconsistencies with dates passed back from ExecuteSQ vs its regular dates. Thus, you can't do regular date math with a FMP Date and an ExecuteSQL date until you change one of the two dates.

               

              So, until FMI can unify date formats somewhat (at least, please!), it's good to have a couple Custom Functions handy for date issues.

              • 4. Re: date format issue
                nhughes@iafrica.com

                Thanks John

                 

                I'm a bit of a newbie and using the calculated script is a wee bit above my pay grade. Nevertheless this is the encouragement I need to help me expand my limited FM skill level.

                • 5. Re: date format issue
                  Johan Hedman

                  You should read the free FileMaker Training Series to learn more about FileMaker

                  http://www.filemaker.com/learning/training/fts.html

                  • 6. Re: date format issue
                    nhughes@iafrica.com

                    Hi Tom thanks for this. I just did a quick test on changing the regional settings before (USA) an after import (UK) and it works!!! I normally only import at month ends but I will attempt the calculated scripting. Best Neil

                    • 7. Re: date format issue
                      nhughes@iafrica.com

                      Thanks John but I do use the Training Series and also lynda training site. Guess I'm just a little slow in grasping the technicalities.  Best Neil