Title
Problem with Date Formats - Easy Fix?
Post
Hey Gang,
I've got a problem with my "Date" field. It's set to auto-enter the create date. The current format of the field is:
Field Type: Date
Filed Options: Auto-enter "Creation" Date
In Layout mode, in the inspector, the "date" field is set with:
Data formatting: Date
12/25/2003
However, for some reason, there continues to be entries showing up (either from previous imports, or erroneous entry, not sure), that will show up as a "?" in my view. When I click in the field where the ? is ... the date format will be like:
19-Apr-11
I can simply manually change it to 4/19/11 ... but this is taking forever, I have tens of thousands of entries that have a ? ... and they don't even sort properly ... if I sort by date, they are all scattered around.
Is there a simply fix to find all these dates in that dd-month-yy format and have them corrected automatically w/ a mass format? I thought that by selecting the data format in the layout menu this would do it, but no such luck.
It would seem that you have text imported into this field from another source. Using text for the month name isn't a valid format for entering a date in FileMaker so you get the question mark.
Try this experiment on a copy of your file to see if it works:
enter find mode and put a ? in this field, then perform a find. This should find all records with invalid dates in this field.
Try this expression in a Replace Field Contents operation with the calculation option to see if it works:
Let ( [ D = YourTable::YourDateField ; Day = Left ( D ; 2 ) ; Month = Middle ( D ; 4 ; 3 ) ; Year = 2000 + Right ( D ; 2 ) ] ;
Date ( Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Month ) / 3 ) ; Day ; Year )
)
I am assuming that all the invalid dates follow the same format, which may not be the case here. I'm also hoping that YourTable::YourDateField will return the actual text imported, not the ?. If it doesn't, you might try getastext ( YourTable::YourDateField) to see if that works.