FormatAsDate ( text )

Returns the given text as the valid complete formatted date: gg/mm/aaaa

Average rating: 4.2 (33 votes) Log in to vote

Daniele Raybaudi - Show more from this author
ACI
http://www.aci.it

Sample input:

FormatAsDate ( "1103" )

Sample output:

01/01/2003

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

/* 
Custom Function: FormatAsDate

Author:
Daniele Raybaudi email: raybaudi@libero.it

Version:
1.0.2

Format:
FormatAsDate ( text )

Parameter:

text - any text expression or text field
Accept inserted text as:
American Settings:       m/d; mm/d; m/dd; mm/dd; mdyy; m/d/yy; m/dd/yy; mm/d/yy; mmddyy; mm/dd/yy; m/d/yyyy; mmddyyyy; m/dd/yyyy; mm/d/yyyy; mm/dd/yyyy 
International Settings: d/m; dd/m; d/mm; dd/mm; dmyy; d/m/yy; d/mm/yy; dd/m/yy; ddmmyy; dd/mm/yy; d/m/yyyy; ddmmyyyy; d/mm/yyyy; dd/m/yyyy; dd/mm/yyyy

Result:
Pseudo-Date(text): to use the result for date calculation, you'll need GetAsDate(Result) 

Description:
Returns the given text as the valid complete formatted date: mm/dd/yyyy (for american systems) or dd/mm/yyyy (for international systems)  ;
any invalid entry (not a date) returns: error 
BTW this isn't a recursive function, so you can use it as a calc !

november 11,  2005 (revision of february 17, 2006)
*/

Let ([
LD = Length ( text ); 
F = Filter ( text ; "-/" );
LF = Length ( F );
F2 = Filter ( text ; "-/0123456789" );
LF2 = Length ( F2 )
];
Case (
LD = 0 ; "" ;
LD ≠ LF2 ; "error" ;
LF > 2 ; "error";
LF ≥  1 and IsValid ( GetAsDate ( text ) ); GetAsDate ( text ) ;
LF2 = 8 and IsValid ( GetAsDate ( Left ( text ; 2 ) & "/" & Middle ( text ; 3 ; 2 ) & "/" & Right( text ; 4 ))) ; GetAsDate ( Left ( text ; 2 ) & "/" & Middle ( text ; 3 ; 2) & "/" & Right( text ; 4 ));
LF2 = 6 and IsValid ( GetAsDate ( Left ( text ; 2 ) & "/" & Middle ( text ; 3 ; 2 ) & "/" & Right( text ; 2 ))) ; GetAsDate ( Left ( text ; 2 ) & "/" & Middle ( text ; 3 ; 2) & "/" & Right( text ; 2 ));
LF2 = 4 and IsValid ( GetAsDate ( Left ( text ; 1 ) & "/" & Middle ( text ; 2 ; 1 ) & "/" & Right( text ; 2 ))) ; GetAsDate ( Left ( text ; 1 ) & "/" & Middle ( text ; 2 ; 1) & "/" & Right( text ; 2 ));
"error"
)
)

Custom Function: FormatAsDate

Author:
Daniele Raybaudi email: raybaudi@libero.it

Version:
1.0.2

Format:
FormatAsDate ( text )

Parameter:

text - any text expression or text field
Accept inserted text as:
American Settings: m/d; mm/d; m/dd; mm/dd; mdyy; m/d/yy; m/dd/yy; mm/d/yy; mmddyy; mm/dd/yy; m/d/yyyy; mmddyyyy; m/dd/yyyy; mm/d/yyyy; mm/dd/yyyy
International Settings: d/m; dd/m; d/mm; dd/mm; dmyy; d/m/yy; d/mm/yy; dd/m/yy; ddmmyy; dd/mm/yy; d/m/yyyy; ddmmyyyy; d/mm/yyyy; dd/m/yyyy; dd/mm/yyyy

Result:
Pseudo-Date(text): to use the result for date calculation, you'll need GetAsDate(Result)

Description:
Returns the given text as the valid complete formatted date: mm/dd/yyyy (for american systems) or dd/mm/yyyy (for international systems) ;
any invalid entry (not a date) returns: error
BTW this isn't a recursive function, so you can use it as a calc !

Comments

		Doug Hogg, Los Angeles Apr 2, 2010
This function was a big help with the conversion of a solution from an Omnis database. By modifying this function, I was able to accept the date input patterns that users have been used to on the old system. For example, they like to enter only a 2-digit year or a month and 2 digit year. Thanks. :-)

		Brett Davidson, Hemet Jul 16, 2018
Hello, I am trying to use your custom function, but MasterDate = 8/7/2018 ______________________ FormatAsDate (MasterDate + 1) I get my date as 8/8/2018 but I want 8/8 How do I do this?

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

FormatAsDate ( text )

Comments

Support this website.

Support this website.