4 Replies Latest reply on May 24, 2013 8:59 AM by philmodjunk

    Easiest Way to Change Date Format



      Easiest Way to Change Date Format


           I've imported an excel file and a column that had a date in it was formatted as general. When it came into FileMaker it came into a date field and is formatting as 05/05/2013. Even when I change the format of the date field in FileMaker as no leading characters for date and month it still appeares with two-digit days and months. I need the date to appear as 5/5/2013.

           Is there an easy way to update all the records (over 200,000) or is it easier to re-import the data with the field in excel correctly formatted as a short date field? Looking for a quick and easy fix. Thanks!

           By the way, using FMPro 11 Advanced.


        • 1. Re: Easiest Way to Change Date Format

               Not sure about this...wondering if a looping script with Set Variable, SetField would trigger the entry to the format desired.  Looping 200K records for a single field adjust wouldn't take too long.

               Give it a try.

               If not, reimport ought to work too.

               Surely there's a better way, but when brute force only takes a couple of minutes, why look for elegant?

          • 2. Re: Easiest Way to Change Date Format

                 Even simpler than a looping script would be replace field contents, but are you sure that your field is of type date?

            • 3. Re: Easiest Way to Change Date Format

                   Yes, I'm positive it is of date type, double checked that. I thought about replace field contents but I wasn't sure how to format it, especially since the dates are all so random. Would I have to do a search for each specific date and change them and then go through the entire list? Or is there a way to do it with a calculated result?

              • 4. Re: Easiest Way to Change Date Format

                     The idea for using replace field contents is to avoid needing a loop or any record specific special handling. But if the data in the field is of type date, it should correctly display the desired format simply by changing the data format options for the field in the Inspector.

                     If you still find that this does not work, you might try this:

                     Show all records

                     Put the cursor in the date field and select replace field contents.

                     Select the Calcualtion option and see if this expression fixes the issue:

                     GetAsDate ( GetAstext ( YourTable::YourdateFieldHere ) )