4 Replies Latest reply on Oct 14, 2009 12:09 PM by comment_1

    Importing date format FMP 9



      Importing date format FMP 9


      I am importing data from excel with a date column.  I have custom formatted the excel to match the layout in FMP.  In the excel file it is YYMMDD, once imported it is displayed MM/DD/YYYY.  Why is it changing? The data that was hand keyed shows the YYMMDD.  It is only happening on the import.





        • 1. Re: Importing date format FMP 9
             The way a field is formatted on a layout is irrelevant here. What matters is the date format being used by the file. This affects data entry as well as importing.

          The date format used by the file is either the short-date format of the OS under which the file was created (or cloned), or the short-date format of the current OS (depending on the setting in File > File Options… > Text).
          • 2. Re: Importing date format FMP 9

            From observation I've concluded the most often used date format is MM/DD/YYYY. However, the order is less important that the "YYYY" . . . yes, 4 digits. This seems universal these days. After importing data with two digit years I would run a script to update the data to four digit years. Also, to extract data using the "Left", "Right" or "Middle" functions, consistency of date format entry and storage is crucial.


            My two cents . . . 

            • 3. Re: Importing date format FMP 9

              I went to the File Options changed the toggles on all three and tried to import the same spreadsheet and it did not keep the current date field in FMP 9.  It still went from YYMMDD to MM/DD/YYYY.  Any other suggestions?



              • 4. Re: Importing date format FMP 9

                I am not sure we are on the same page: you can import a date in YYMMDD format directly into a date field only if your file is set to use the YYMMDD format. In File Options you merely switch between the file's saved format and the current OS format - but if neither one of them is YYMMDD, this won't help.


                Perhaps it would be easier to import the date into a text field, and use a calculation to parse it out into a true date.