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.
The data type is also Date as it shows up as the date 0/0/00
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.
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.
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.
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
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.
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.
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.
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.
The number 38686 corresponds to the number of days to the date since 12/31/1900
And is that a date for the year 1905 or 2005?
GetasDate (ImportedField + 693594 )
Should work. GetAsDate ( 38686 + 693594 )
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.
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?
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).