Date formatting in FileMaker

The importance of localization

1. October 2020In Filemaker ExamplesBy Karsten Risseeuw6 Minutes

The local information in each FileMaker file

Dates in Europe and, for example, in the states are different.

In Europe, the specification “01.10.2020” is interpreted as “1 October 2020″,
but in the States as ” January 10, 2020 “.

In order for FileMaker to understand how to interpret a date specification, the local information is anchored as a fixed specification in the file when a file is created. This allows the date to be interpreted, but should the file be opened on another operating system, FileMaker can synchronize new date entries (between system settings and the information anchored in the file). This ensures the integrity of different date entries.


Because there are different interpretations of dates and times worldwide, the operating system manages different options. FileMaker puts the system settings for formatting (the local information) into the file as soon as it is created.

Regional settings

FileMaker sets a handle to interpret the date, and this fixpoint (or local information) does not change if the file is opened on a system with different date formats. Internally, the anchor remains in place even if a date is entered differently due to a different operating system.

If a FileMaker file is created in Europe but then opened in the USA, the following situation occurs: Internally in the file, the date interpretation has been set to the European DAY/MONTH/YEAR. However, for users in the USA, the system settings are MONTH/DAY/YEAR. In date fields this is no problem – FileMaker can adjust the representation (!). Internally, however, the European structure is retained. This can lead to problems.


Date calculations are unpredictable

Problems occur with date calculations if the settings of the file are different from those of the user. Two sample files are provided here as download: It is the same file, but once as a file with European reference and once as a file with American reference. If you open a file, the following happens:

If you open a file that is not part of your system settings, the following happens: Dates written to text fields get the file internal formatting (which is wrong). There are also problems with date calculations. The best way to see this is to open both files in parallel.


In this example: The system runs with EU settings. The EU file is OK.

In this example: The system runs with EU settings. However, the file was created with American settings. Now the date calculations are no longer OK.

Neutral storage of dates

A date can not only be saved as “Date”, but also as “Date Number”. FileMaker counts the days from January 1 of the year 0001, so any date since then can be represented as a number. This number is not dependent on any formatting. The date can therefore be defined as numbers:

ReadAsNumber ([DATE])

To be able to convert this number back to the date, the following applies:

ReadAsDate ( [NUMBER] )

With these two specifications, dates can be stored neutrally, or transferred as a number from one file to the next. When converting back to a date, the date settings of this file are applied where it is interpreted.

October 1, 2020 has the number “737699”. In a European file it is converted to ” 01/10/2010″, while in an American file it is converted to ” 10/01/2010″.


Removal of local information

The local information that FileMaker maintains when you create a file cannot be modified. There is only one way to “reset” the file, and that is by creating an empty clone of the file.

In the example file, there is a button that makes it very easy to create such a clone. The local information of the file is reset when the file is opened and therefore depends on the system the user is using. With the clone function it is relatively simple to test the behavior in different system environments.


To program a neutral date

No FileMaker file today is neutral in terms of date processing. However, one can consider whether one should strive for a neutral development of new solutions (using the date number).

New features since FileMaker Pro 19.1

Claris has added two important features in the latest version:

Get ( SystemLocaleElements )
Get ( FileLocaleElements )

With these two functions you get a complete overview of all settings, neatly packed in JSON – once from the system and once from the file. From this, for example, the current data can be retrieved from the system and used for feedback, formatting or other purposes.

It’s all about system settings and file settings. Of course, programmed features, such as language settings of a multilingual solution, are not included.


Download example file