@GetAsDate ( text )

Multilingual wrapper for the native GetAsDate() function to handle month names, abbreviations, and ISO-style dates.

Be the first to rate this function Log in to vote

Alex Zueiv - Show more from this author
Tashkent
https://github.com/zueiv

Sample input:

UniqueValues ( List (
@GetAsDate ( "March 31, 2022" ) ; // US required
@GetAsDate ( "2022mar31" ) ;
@GetAsDate ( "31 maart 2022" ) ; // Nederlands required
@GetAsDate ( "31 mrt 22" ) ;
@GetAsDate ( "2022-3-31" ) ; // no specific locale required
@GetAsDate ( "2022年3月31日" ) ;
@GetAsDate ( "2022년 3월 31일" ) ;
) )

Sample output:

31-03-2022 // if the file locale is Netherlands and the system locale is US

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

/**
* @GetAsDate ( text )
* 
* Multilingual wrapper for the native GetAsDate() function. Converts text
* containing a month name or abbreviation to a date using the file and
* system locale definitions. If the month is specified as a number, uses
* the active locale (Use System Formats menu selection) to distinguish it
* from the day. Supports the ISO date format and text formats used in
* China, Japan, and Korea.
*
* Requires FM 19.1.2 or higher.
*
* Usage example (returns 31-03-2022 if the file locale is Netherlands and
* the system locale is US):
*
	UniqueValues ( List (
		@GetAsDate ( "March 31, 2022" ) ; // US required
		@GetAsDate ( "2022mar31" ) ;
		@GetAsDate ( "31 maart 2022" ) ;  // Netherlands required
		@GetAsDate ( "31 mrt 22" ) ;
		@GetAsDate ( "2022-3-31" ) ;      // no specific locale required
		@GetAsDate ( "2022年3月31日" ) ;
		@GetAsDate ( "2022년 3월 31일" ) ;
	) )
*
* Created 2022-07-12 by Alex Zueiv
* https://www.briandunning.com/cf/2601
**/


Let ( [

// parse parameter
dateList = While (
	// extract groups of digits and letters, separating them with spaces
	[
		size = Length ( text ) ;
		i = 0 ; pd = 1 ; r = ""
	] ;
	i < size ;
	[
		i = i + 1 ;
		c = Middle ( text ; i ; 1 ) ;
		d = Filter ( c ; "1234567890" ) ≠ "" ;
		l = not Exact ( Upper ( c ) ; Lower ( c ) ) ;
		r = r & If ( d ≠ pd ; " " ) & If ( d or l ; c ; " " ) ;
		pd = d
	] ;
	// …and convert to list
	Substitute ( TrimAll ( r ; 1 ; 1 ) ; " " ; ¶ )
) ;

fileLocale = Get ( FileLocaleElements ) ;
systemLocale = Get ( SystemLocaleElements ) ;
activeLocale = If ( JSONGetElement ( systemLocale ; "Misc.Active" ) ; systemLocale ; fileLocale ) ;

// convert month name or abbreviation to number
monthList = Substitute (
	Lower ( List (
		JSONListValues ( fileLocale   ; "Date.DMQ.Months.AbbrvList" ) ;
		JSONListValues ( systemLocale ; "Date.DMQ.Months.AbbrvList" ) ;
		JSONListValues ( fileLocale   ; "Date.DMQ.Months.NameList" ) ;
		JSONListValues ( systemLocale ; "Date.DMQ.Months.NameList" )
	) ) ;
	"." ; ""
) ;
// skip the day abbreviation if present, because "mar" is Tuesday in French, Spanish, and Italian
monthNameOrAbbrv = RightValues ( FilterValues ( dateList ; monthList ) ; 1 ) ;
monthNum = If ( monthNameOrAbbrv ≠ "" ;
	Mod (
		ValueCount ( Left (
			monthList ;
			Position (
				¶ & monthList & ¶ ;
				¶ & monthNameOrAbbrv ;
				1 ; 1
			)
		) ) - 1 ;
		12
	) + 1
) ;

// determine date format
dateNums = TrimAll ( Substitute (
	Filter ( dateList ; "¶1234567890" ) ;
	¶ ; " "
) ; 1 ; 1 ) ;
firstElement = Left ( JSONGetElement ( activeLocale ; "Date.DateOrderName" ) ; 1 ) ;
yearFirst = Length ( LeftWords ( dateNums ; 1 ) ) = 4 or ( not monthNum and firstElement = "Y" ) ;
yearOffset = Case (
	yearFirst ; 1 ;
	monthNum ; 2 ;
	3
) ;
monthOffset = Case (
	monthNum ; "" ;
	yearFirst ; 2 ;
	1 + ( firstElement = "D" )
) ; 
dayOffset = Case (
	monthNum ; 1 + yearFirst ;
	yearFirst ; 3 ;
	1 + ( firstElement = "M" )
) ; 

// create date
theDate = GetAsDate (
	MiddleWords ( dateNums ; yearOffset ; 1 )
	& "+" & If ( monthNum ; monthNum ; MiddleWords ( dateNums ; monthOffset ; 1 ) )
	& "+" & MiddleWords ( dateNums ; dayOffset ; 1 )
)

] ;

// apply native function if conversion fails
If ( theDate = "?" ; GetAsDate ( text ) ; theDate )

)

Multilingual wrapper for the native GetAsDate() function.

Converts text containing a month name or abbreviation to a date using the file and system locale definitions. If the month is specified as a number, uses the active locale (Use System Formats menu selection) to distinguish it from the day.

Supports the ISO date format and text formats used in China, Japan, and Korea.

Requires FM 19.1.2 or higher.

Comments

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

@GetAsDate ( text )

Comments

Support this website.

Support this website.