I imported some data and one field was a date. It was imported as 20160630. How would I format this to make it into a date? Do I need to create a new field? Is there a formula?
FileMaker stores date differently depending what local date format you use. For me I use YYYY-MM-DD, but in other countries there are other ways like MM/DD/YYYY for example. You need to have the date in your file that you import as the same format as you have in your FileMaker database.
Thank you. Its already imported, but as a string of numbers. Any way to convert it into a date when already in FileMaker? Kind regards.
Then you can use the function Replace and do a Calculation where you do
date = YourDateField
// Date( Month ; Day ; Year )
Date( Middle( date ; 5 ; 2 ) ; Right( date ; 2 ) ; Left( date ; 4 ) )
And that will calculate the numeric numbers into date format
Note that Johan is referring to the Records Menu command to 'Replace Field Contents' (not the Find/Replace function in the Edit menu).
Take a backup - there is no 'Undo' function!
You could test your formula first by Finding one record and Replace Field Contents for it.
I wanted to add that it sounds like you might be working with a text field.
If that's the case, you should consider using a date field instead.
"string" (of numbers) is the optimum word here. As Johan shows, you can "parse" text (even if 'numbers').
I've seen imported "dates" (_as_ numbers) not convert so easily. FM uses "1/1/0001" as Day One (the number "1"). Excel uses "1/1/1900" as Day One (the number "1").
Using any of the parsing, you can auto-enter a calc to test if IsValid(), then leave as is, otherwise convert via parsing. But otherwise "cleaning data after import" may also be necessary. And heed Alan's advice:
backup. I might even have a temporary field set with this kind of cleaned data to verify that I haven't used the wrong calc!
Retrieving data ...