FormatDateLocale ( thedate ; formatstring ; LocaleID )

Formats a date according to a Format string and a given language ID

Average rating: 3.6 (43 votes) Log in to vote

Theo Ros - Show more from this author

Sample input:

FormatDateLocale ( Date ( 7 ; 9 ; 2005 ) ; "DDDD, D MMMM YYYY" , "nl" )

Sample output:

"zaterdag, 9 juli 2005"

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/* -------------------------------------------------------------------------------
Formats a date according to a Format string and a given language ID:

In: thedate - a field or value containing a date
      formatstring - a string like "DDDD, MMMM D YYYY"
      LocaleID - a string containing the Language ID
        currently supported:
        "nl" -> Dutch
        "en" -> English
        "ge" -> German
        "fr" -> French
        "es" -> Spanish

formatstring can hold the following patterns:
  "YYYY"   : 4 digit year 
  "YYY"     : same as "YYYY"
  "YY"       : 2 digit year 
  "Y"         : 1 digit year 
  "MMMM" : Full month name 
  "MMM"   : Short month name 
  "MM"     : 2 digit month (leading 0) 
  "M"        : 1 or 2 digit month 
  "DDDD" : Full weekday name
  "DDD"    : Short weekday name
  "DD"      : 2 digit daynumber (leading 0) 
  "D"        : 1 or 2 digit daynumber 

Return type: Text

FormatDateLocale ( Date ( 7 ; 9 ; 2005 ) ; "DDDD, D MMMM YYYY" , "nl" )
returns "zaterdag, 9 juli 2005"

For Month and Day names, the function uses the functions DayNameList() 
and MonthNameList() to get the names in the specified language.
If the LocaleID parameter is an empty string, the systems Language is used.

Theo Ros
------------------------------------------------------------------------------- */ 

Let ( [ daylist = DayNameList ( LocaleID ) ;
           monthlist = MonthNameList ( LocaleID ) ; 
           dte = GetAsDate ( thedate ) ;
           dow = DayOfWeek ( dte ) ;
           m = Month ( dte ) ;
           y = Year ( dte ) ] ;
  Substitute ( Upper ( formatstring ) ; 
    [ "YYYY" ; y ] ; 
    [ "YYY" ; y ] ; 
    [ "YY" ; Right ( "00" & Mod ( y ; 100 ) ; 2 ) ] ; 
    [ "Y" ; Right ( y ; 1 ) ] ; 
    [ "MMMM" ; Substitute ( MiddleValues ( monthlist ; m ; 1 ) ; "¶" ; "" ) ] ; 
    [ "MMM" ; Substitute ( MiddleValues ( monthlist ; m + 12 ; 1 ) ; "¶" ; "" ) ] ; 
    [ "MM" ; Right ( "00" & m ; 2 ) ] ; 
    [ "M" ; m ] ; 
    [ "DDDD" ; Substitute ( MiddleValues ( daylist ; dow ; 1 ) ; "¶" ; "" ) ] ; 
    [ "DDD" ; Substitute ( MiddleValues ( daylist ; dow + 7 ; 1 ) ; "¶" ; "" ) ] ; 
    [ "DD" ; Right ( "00" & Day ( dte ) ; 2 ) ] ; 
    [ "D" ; Day ( dte ) ] 
  )
)

Formats a date according to a Format string and a given language ID:

In: thedate - a field or value containing a date
formatstring - a string like "DDDD, MMMM D YYYY"
LocaleID - a string containing the Language ID
currently supported:
"nl" -> Dutch
"en" -> English
"ge" -> German
"fr" -> French
"es" -> Spanish

formatstring can hold the following patterns:
"YYYY" : 4 digit year
"YYY" : same as "YYYY"
"YY" : 2 digit year
"Y" : 1 digit year
"MMMM" : Full month name
"MMM" : Short month name
"MM" : 2 digit month (leading 0)
"M" : 1 or 2 digit month
"DDDD" : Full weekday name
"DDD" : Short weekday name
"DD" : 2 digit daynumber (leading 0)
"D" : 1 or 2 digit daynumber

Return type: Text

FormatDateLocale ( Date ( 7 ; 9 ; 2005 ) ; "DDDD, D MMMM YYYY" , "nl" ) returns "zaterdag, 9 juli 2005"

For Month and Day names, the function uses the functions DayNameList() and MonthNameList() to get the names in the specified language.
If the LocaleID parameter is an empty string, the systems Language is used.

Theo Ros

Comments

		Vogel, Heidelberg Mar 6, 2009
There is a problem here in that the function will replace the letters "D" and "M" in day or month names with the respective day or month number. For example, FormatDateLocale ( "27.05.2007" ; "DD MMMM YYYY" ; "en" ) will produce 27 5ay 2007 and FormatDateLocale ( "27.12.2007" ; "DD MMMM YYYY" ; "en" ) will result in 27 27ecember 2007 This can obviously be avoided by using escape characters (eg "#") around the formatstring patterns and parsing the formatstring accordingly: "#MMMM#" instead of "MMMM" "#DD#" instead of "DD" etc Thanks for the function though. I work in a mixed language environment... Cheers, V.

		Adrian May 9, 2009
Good solution. Only one escape character, not two, need be used. It's simpler: "#MMMM" instead of "MMMM"

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

FormatDateLocale ( thedate ; formatstring ; LocaleID )

Comments

Support this website.

Support this website.