1 2 3 4 Previous Next 46 Replies Latest reply on Nov 27, 2013 9:42 AM by philmodjunk

    Format for importing dates from excel into FMP

    RishiTrikha

      Title

      Format for importing dates from excel into FMP

      Post

           I am trying to import a column of dates into FMP. I have chosen the field type as date and formatted it as MM/DD/YY using the inspector. I understand that in excel, the dates are formatted such that it counts the number of days since Jan 1, 1901. therefore when I try to import the dates into FMP, it shows up as the number of days in FMP. When I tried to change it into a date format, all dates showed up as 0/0/00. I need to show the dates so that I can calculate the difference between two different dates and have so many dates that it would take too long to enter it manually.
           I am having the same trouble trying to import time into FMP

        • 1. Re: Format for importing dates from excel into FMP
          philmodjunk

               and formatted it as MM/DD/YY using the inspector.

               The layout's field format has no affect on how the data is imported.

               

                    when I try to import the dates into FMP, it shows up as the number of days in FMP. When I tried to change it into a date format, all dates showed up as 0/0/00.

               But what data type is the FIleMaker field into which you are importing? Text, Number or Date? This you can check in Manage | Database | Fields.

          • 2. Re: Format for importing dates from excel into FMP
            RishiTrikha

                 The data type is also Date as it shows up as the date 0/0/00

            • 3. Re: Format for importing dates from excel into FMP
              philmodjunk

                   That has me puzzled as you should not need to "change it into a date format" if the field is of type date.

                   As far as I know, Importing dates from Excel into a date field in FileMaker should result in correct import of the data.  I ran a quick test, creating an xslx file in Excel and then importing the data into FileMaker 12. The screen shots show the result.

              • 4. Re: Format for importing dates from excel into FMP
                RishiTrikha

                     Thank you, I too ran a quick test and it worked for me as well. However, I believe my problem lies in the fact that in my microsoft Excel file, the date is formatted as a number, however when it appears that I have changed it to be a date format, it still imports into FMP as a number (eg, 38756). I need to calculate the number of days of a certain category by subtracting two date fields, and I'm not sure how FMP would do this without converting the dates into a number format, however I do not want the dates to show up as a number format.

                • 5. Re: Format for importing dates from excel into FMP
                  philmodjunk

                       FIleMaker internally stores dates much the same as Excel. The only difference is that FileMaker dates record the number of days since 12/31/000 instead of 12/31/1900--assuming that what you posted earlier is correct about Excel. When a date is imported, FileMaker converts the value for you as long as it can "recognize" the data as being of type date.

                       Because date fields in FileMaker are integers recording days since 12/31/0000, you can compute elapsed days by simply subtracting the two dates. Just as you do in Excel.

                  • 6. Re: Format for importing dates from excel into FMP
                    RishiTrikha

                         Yes, that is precisely what I meant. I see, interesting. For somereason the date is still showing up as 0/0/00, I'll try to continue to troubleshoot, thank you

                    • 7. Re: Format for importing dates from excel into FMP
                      philmodjunk

                           I don't think that is precisely what you meant or the dates should be importing without problem. I would take a closer look at the actual data in the Excel file. There might be a quote that is causing the date to be text or the date's format is not a simple MM/DD/YYYY value and FileMaker is interpreting the text form of the date instead of the value.

                           Note that in my test I just typed in simple MM/DD/YYYY dates (which are consistent with my US based locality settings on computer and in database.) into my spreadsheet file. I didn't for example, have month names or such as part of the date.

                      • 8. Re: Format for importing dates from excel into FMP
                        RishiTrikha

                             As did my test run, however I am working with a file that I did not create. Furthermore I am working with OpenOffice and not Microsoft excel, would this make a difference? I still save the file as .xls format.

                        • 9. Re: Format for importing dates from excel into FMP
                          philmodjunk

                               Try saving it into xlsx format and see if that makes a difference.

                               Even though you did not create the file, you can open it in Excel or OpenOffice and examine its contents and design. I'd check for anything unusual about that date column.

                               If all else fails, it may be possible to import into a text field and use an auto-enter calculation or a script to convert the text into a date and put it in your date field. You might try importing this column into a text field to see what you get. If it looks like the correct date (but is text) then we can set up a simple conversion calculation that can convert text to date and you can move on.

                          • 10. Re: Format for importing dates from excel into FMP
                            RishiTrikha

                                 I tried saving it into an xlsx format, but that did not work. When I import it into a text field a number shows up, ex: 38686 shows up for the date 11/30/05, could you teach me how to write the script that would convert that text into a date.

                                 Thank you

                            • 11. Re: Format for importing dates from excel into FMP
                              RishiTrikha

                                   The number 38686 corresponds to the number of days to the date since 12/31/1900

                              • 12. Re: Format for importing dates from excel into FMP
                                philmodjunk

                                     And is that a date for the year 1905 or 2005?

                                     Assuming 2005,

                                     GetasDate (ImportedField + 693594 )

                                     Should work. GetAsDate ( 38686 + 693594 )

                                     returns 11/30/2005

                                     There are two simple ways to use this calculation. Replace field Contents can use the value from the field, and put the computed date into your date field.

                                     Or you can set up the date field with this calculation as an auto-enter calculation and then, if you  enable auto-enter operations during import, this conversion can take place automatically with each import.

                                • 13. Re: Format for importing dates from excel into FMP
                                  RishiTrikha

                                       The number 38686 corresponds to the number of days to the date 11/30/05 since 12/31/1900. Also, I am having the same problem importing times. For instance if I try to import a time 11:58:59 into FMP, and set the field type as Time, I get the time 0:00:00, however if I set the field type as Text, it shows up as 0.49929.. (which I'm not sure what corresponds to). Would you happen to know what this number corresponds to and if so could you teach me how to write a script to convert this decimal into the proper time?

                                       Thank you.

                                  • 14. Re: Format for importing dates from excel into FMP
                                    RishiTrikha

                                         That is correct, it would be 2005, I apologize but I am new to setting up scirpts so I am a bit confused to how to set it up (even with your instructions).

                                    1 2 3 4 Previous Next