Brian Dunning's FileMaker Custom Functions

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   Rewolfer
http://rewolfer.de

Share on Facebook Share on Twitter

  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: 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   Lee Smith
Apr 22, 2012
Your example IsDate ( "23/11/2003" ) returns a blank output?
 
Lee Smith   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   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   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.
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.