1 Reply Latest reply on Jul 25, 2009 2:36 AM by comment_1

    US timestamp field changes to UK timestamp format in calculated field (Filemaker 10)



      US timestamp field changes to UK timestamp format in calculated field (Filemaker 10)


      I have a calculated field (DateRecNo) that I use to link my databases and tables, consisting of the DateCreated field (a timestamp) plus the serial number of the record (DateCreated & "-" & RecordNumber). I've bounced back and forth between system formats for the dates in the past, and now my problem is that DateRecNo stubbornly continues to display in the UK format, despite the fact that the Date Created field is in US format (and my system format is now set to the US format). This has not been a problem in the past 3 years-- but it's the beginning of the new year for this database and in the process of importing records into a new template, this problem has just surfaced. I'm using a Mac Mini with OS X and just updated my FM from 10.0 to 10.0.3.


      So if DateCreated is: 8/26/2005 11:01:02 PM

      DateRecNo is still showing up as: 26/08/2005 23:01:02-189


      ... and the link to tables that use the US date format doesn't work. 


      Things I've tried after looking around on the Forum:

      1. Deleting and re-creating the DateRecNo field

      2. Setting the System date/time format to South Africa (which uses year/month/day), and then back to US format

      3. Writing a new calculated field as:

          Month(Date created) & "/" &
          Day(Date created) & "/" &
          Year(Date created) & " " &
          Timestamp(Date created;Date created) & "-" &

      ... but this shows up as 8/26/2005 26/08/2005 23:01:02-189 because the Timestamp function still gets the UK format 

      4. Setting a startup script to UseSystemFormats=On for this database. File Prefs are also set to "always use system settings".

      5. Changing the storage options for DateRecNo to "do not store; recalculate as needed" (no matter how I set this up, it doesn't change the date format)

      6. Saved the database in compacted format 

      7. Changing the DateRecNo formula to GetAsTimestamp(Date created) & "-" & RecordNumber 


      I'm out of ideas and getting frustrated. Why won't Filemaker use the correct date format in the calculated field? 

        • 1. Re: US timestamp field changes to UK timestamp format in calculated field (Filemaker 10)

          I suggest you (a) save a copy of your file as a clone (no records), (b) fix existing data to conform to your preferred format, and (c) import it into the clone.



          A calculation =


          GetAsTimestamp ( GetAsNumber ( YourOldTimestampData ) )


          has a good chance of converting the old data to the current format - provided the file isn't messed up to a point where the old timestamps aren't recognized anymore as timestamp at all. Also watch out for day and month being interchanged.



          Another way to normalize your data to a common standard is to format the timestamp field on a layout as m/d/y h/m/s, then export as text while selecting the 'Apply current layout's data formatting…' option.