IsDate ( _var )

return valid date as number of days since 0001-01-01 or nothing if invalid

Average rating: 4.8 (22 votes) Log in to vote

Rewolfer - Show more from this author
http://rewolfer.de

Sample input:

IsDate ( "2003-11-23" )
IsDate ( "23.11.2003" )
either
IsDate ( "11/23/2003" )
or
IsDate ( "23/11/2003" )

Sample output:

731542

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

/*=================================================

FORM:		IsDate ( _var )

PURPOSE:	 return valid date as number of days since 0001-01-01 or nothing if invalid

PARAMETER:	_var	type: unknown	value: unknown
		valid date: "2003-1-23" / "2003-01-01" / "2003-1-1" / "23. 1.2003" / " 1/23/2003"
		invalid date: "03-10-01" / "30.02.2003" / "20030123" / 735223 (FMP internal format)

RESULT:		number of days since 0001-01-01 or nothing

DEPENDENCY:	-

NOTE:		to import foreign dates set "auto calculated value" of your date field to "GetAsDate ( IsDate ( Self ))"
		to get date according to your system-/file-settings write GetAsDate ( IsDate ( _dateInQuestion ))
		requires 4-digit-year (otherwise what is 10/10/10)
		whitespace is tolerated
		accepted formats: "Y-M-D" and "D.M.Y" and either "M/D/Y" (default) or "D/M/Y"
		you have to set $$IsDateFormatPreferred to "D/M/Y" via script if you prefer UK-dates instead of US-dates and vice versa (so 1/4/2003 => 2003-04-01)

EXAMPLE:	IsDate ( "11/23/2003" ) => 731542

HISTORY:	2012-04-12 [103032] 	1.00 	tsw 	decided to bring the date-check in its own function
		2012-04-16 [213412] 	2.00 	tsw 	checks all date formats formally and returns either date as a number or nothing
		2012-04-17 [104813] 	2.10 	tsw 	checks for VALID date as well before returning anything
		2012-04-19 [204950] 	2.20 	tsw 	at first run caches the current system format in $$IsDateFormatAccepted
		2012-04-21 [141255] 	2.21 	tsw 	switched to matrix as more reliable
		2012-04-22 [130128] 	2.22 	tsw 	optimized and debugged

SOURCE:		tsw @ http://rewolfer.de
=================================================*/

Let ( 
    _clean = Filter ( _var ; "0123456789./- ");

    Case ( 
	_clean = _var ; 
	Let ([
	// first run sets $$IsDateFormatAccepted to either "Y-M-D" or "D.M.Y" or "D/M/Y" or "M/D/Y"
	_accepted = Case ( IsEmpty ( $$IsDateFormatAccepted ) ; 
		Let ([ 
			_dateDelimiter = Left( Filter ( Get (CurrentDate) ; "-./" ); 1);
			_dateFormat = Case ( 
				GetAsDate ( "2000-12-31" ) = "2000-12-31" ; "Y-M-D" ; 
				GetAsDate ( "12/31/2000" ) = "12/31/2000" ; "M/D/Y" ;
				GetAsDate ( "31.12.2000" ) = "31.12.2000" ; "D" & _dateDelimiter & "M" & _dateDelimiter & "Y" )
		];
			Evaluate("Let( $$IsDateFormatAccepted = \"" & _dateFormat & "\" ; \"\" )" )));
	_format = $$IsDateFormatAccepted ;
	_delimiter = Left ( Filter ( _var ; "-./" ); 1);
	_vl =  Substitute ( _var ; [ " " ; "" ] ; [ _delimiter ; ¶ ] );
	_vl1 = GetValue ( _vl ; 1 );
	_vl2 = GetValue ( _vl ; 2 );
	_vl3 = GetValue ( _vl ; 3 );
	_matrixed = Case (
		// YMD = 2003-01-25 
		_delimiter = "-" and Length ( _vl1 ) = 4  ; Substitute ( _format ; ["Y" ; _vl1] ; ["M" ; _vl2] ; ["D" ; _vl3]);

		// DMY = 25.01.2003
		_delimiter = "." and Length ( _vl3 ) = 4 ; Substitute ( _format ; ["Y" ; _vl3] ; ["M" ; _vl2] ; ["D" ; _vl1]);

		// MDY = 01/25/2003
		_delimiter = "/" and Length ( _vl3 ) = 4 and $$IsDateFormatPreferred <> "D/M/Y"; Substitute ( _format ; ["Y" ; _vl3] ; ["M" ; _vl1] ; ["D" ; _vl2]);

		// DMY = 25/01/2003; set $$IsdateFormatPreferred via script if you prefer UK-dates over US-dates
		_delimiter = "/" and Length ( _vl3 ) = 4 and $$IsDateFormatPreferred <> "M/D/Y"; Substitute ( _format ; ["Y" ; _vl3] ; ["M" ; _vl2] ; ["D" ; _vl1])
	);
	_dateValid = GetAsDate ( _matrixed )
	];

	Case ( _dateValid <> "?" ; GetAsNumber ( _dateValid ))
	)
    )
)

/*=================================================

FORM: IsDate ( _var )

PURPOSE: return valid date as number of days since 0001-01-01 or nothing if invalid

PARAMETER: _var type: unknown value: unknown
valid date: "2003-1-23" / "2003-01-01" / "2003-1-1" / "23. 1.2003" / " 1/23/2003"
invalid date: "03-10-01" / "30.02.2003" / "20030123" / 735223 (FMP internal format)

RESULT: number of days since 0001-01-01 or nothing

DEPENDENCY: -

NOTE: requires 4-digit-year (otherwise what is 10/10/10)
whitespace is tolerated
accepted formats: "Y-M-D" and "D.M.Y" and either "M/D/Y" or "D/M/Y"
you have to set $$IsDateFormatPreferred to "D/M/Y" via script if you prefer UK-dates instead of US-dates and vice versa (so 1/4/2003 => 2003-04-01)
to import foreign dates set "auto calculated value" of your date field to "GetAsDate ( IsDate ( Self ))"

EXAMPLE: IsDate ( "11/23/2003" ) => 731542

SOURCE: tsw @ http://rewolfer.de
=================================================*/

Comments

		Lee Smith Apr 22, 2012
Your example IsDate ( "23/11/2003" ) returns a blank output?

		Lee Smith Apr 22, 2012
One other point: Although both 03/04/2012 and 04/03/2012 are valid dates, how would you determine which one is correct if they are imported? March 4th April 3rd

		Rewolfer Apr 22, 2012
This depends on your system settings. On US-systems this date would be viewed as the 11th day of month 23 of the year 2003 which is FALSE and thus returns nothing. If you want this British date to be converted to your US-format you have to set the global variable $$IsDateFormatPreferred to "D/M/Y" and you get a result of "731542". No one except you knows what kind of dates you handle. By just looking at 10/1/2001 we could face January or October. Hence the possibility to overrule the system setting with the variable. If you don't overrule "23/11/2003" is correctly invalid. The idea of this (helper-) function is to validate AND convert dates to numbers. Very helpful when importing.

		Rewolfer Apr 22, 2012
To further clarify the output: On all system except British ones "23/11/2003" won't output anything. Only if you overrule with $$IsDateFormatPreferred = "D/M/Y", the custom function accepts the input. Might be unfair for UK-folks, but the user base in the US is simply larger. I use this function a lot to import VALID dates of foreign origin. A Chinese date of "2003-11-23" is auto evaluated to "23.11.2003". If you simply swap the digits I could end up with "2003-0-23" finding its way into the FileMaker-DB.

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

IsDate ( _var )

Comments

Support this website.

Support this website.