Brian Dunning's FileMaker Custom Functions

TextToDate ( AnyText )

Converts any text into a fully-validated FM date (supports US, European and ISO formats)

  Average rating: 4.4 (31 votes) Log in to vote

Doug Staubach   Doug Staubach
FileMaker Poweruser
https://www.linkedin.com/in/dougstaubach

Share on Facebook Share on Twitter

  Sample input:
Dec 21, 2011
6.Mar.14
Nov 28
6/14
2014-12-1 11:22:33 PM
Dec13
Feb232012
  Sample output:
12/21/2011
3/6/2014
11/28/2014
6/14/2014
12/1/2014
12/13/2013
2/23/2012

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

Accepts just about any text string that looks like a date and attempts to convert it into a valid date for Filemaker. Works well in browse mode when combined with a script trigger for "OnObjectValidate" (avoids the silly default validation messages). With some creative scripting, can also be used in the Find screen.

* Tries to automatically recognize US, International, and ISO date formats.
* Will ignore any extra text that appears after the date (like time).
* Works best if the date is split by delimiters (like "-" or " " or "." or "/" or ",")
* This version can also process dates without delimiters (if month is provided as text or format is YYYYYmmDD).
* If only a month and date are provided, it will automatically-add the current year.

Note: If the text cannot be converted to a valid date, the function just leaves the original text as-is (does nothing).

Credit to David Head for his method of converting month days to month numbers (https://www.briandunning.com/cf/805)

 

Comments

Lee Smith   Lee Smith
Feb 9, 2014
You have a typo in your CF

You TextToDate ( text ) is expressed in two places as ANYTEXT in a couple of places.

Other than, a very handy CF for scraping web sites.
 
Lee Smith   Lee Smith
Feb 9, 2014
You have a typo in your CF

Your TextToDate ( text ) is expressed in as ANYTEXT in a couple of places.

Other than, that, this is a very handy CF for scraping web sites.
 
Doug Staubach   Doug Staubach, Denver CO USA
Feb 9, 2014
Hi Lee:

Thanks for the nice review - I'm glad other people find this as useful as I do.

I keep two copies of the input text string on purpose (the original is called ANYTEXT, and the copy is now called MYTEXT). The reason I do this is so I can return the original text unmodified, if the function fails to produce a valid date.
 
Peter beehler   Peter beehler, Valley Forge, PA
Sep 15, 2014
Hi,

A common occurrence is the name of the week also,
For example I run across this commonly,
"Monday, August 25, 2014 11:42 AM"

If in your opening 'my text', you also just substitute out for the days of the week, then your function also resolves this common occurrence

Substitute(text;["Monday";""]; ["Tuesday";""]; ["Wednesday";""]; ["Thursday";""]; ["Friday";""]; ["Saturday";""]; ["Sunday";""]);
 
Doug Staubach   Doug Staubach, Denver, Colorado, USA
Mar 28, 2015
Hi Peter:

Thanks for the suggestion - I incorporated it into the final version.
 
Gary Amstutz   Gary Amstutz, Sweetwater
Nov 12, 2015
It looks like, in your changes on March 28, you entered a bug in the first Substitute line:

MYTEXT = Trim ( Substitute ( GetAsText ( Trim ( ANYTEXT ) ) ; "["Monday" ; "" ] ; "["Tuesday" ; "" ] ; "["Wednesday" ; "" ] ; "["Thursday" ; "" ] ; "["Friday" ; "" ] ; "["Saturday" ; "" ] ; "["Sunday" ; "" ] ; [ "-" ; " " ] ; [ "/" ; " " ] ; [ "." ; " " ] ; [ "," ; " " ] ; [ " " ; " " ] ) ); // strips out common date delimiters

Change this line to:
MYTEXT = Trim ( Substitute ( GetAsText ( Trim ( ANYTEXT ) ) ; ["Monday" ; "" ] ; ["Tuesday" ; "" ] ; ["Wednesday" ; "" ] ; ["Thursday" ; "" ] ; ["Friday" ; "" ] ; ["Saturday" ; "" ] ; ["Sunday" ; "" ] ; [ "-" ; " " ] ; [ "/" ; " " ] ; [ "." ; " " ] ; [ "," ; " " ] ; [ " " ; " " ] ) ); // strips out common date delimiters

and that should fix the bug! Other than that, great function that obviously took you a lot of time... so thanks for sharing your time and hard work with us! :-)
 
Doug Staubach   Doug Staubach, Denver, CO USA
Nov 12, 2015
Hi Gary:

Wow - that was pretty much a "cut-and-paste disaster", thank you for catching it!

I appreciate the positive feedback. Creating custom functions does take some time and effort, but I am happy to share my creations, since so many others have done the same (and their efforts have saved me some time, so I think of it as karma payback loop).

p.s. -- If you perform a search on this site using my last name, you can find some of the other items that I've contributed to this site. I hope you find them useful!
 
Michael Nord   Michael Nord, Malmö, Sweden
Jul 21, 2016
This function always return the ANYTEXT. (I appended "ERROR " to it to make sure that was what was happening). Sweden uses the format YYYY-MM-DD, but it seems this function always tries to use "/" as the delimiter?
 
Jeff Hurst   Jeff Hurst, Canada
Jan 1, 2018
It is unfortunate no one can wave a magic wand to influence others to write out their date in an explicit format with visual clues as to what part is the day, which is the month and year. Y2K taught us the value of being explicit with a four (or five) digit year. With all the software re-coding at the time between countries, the world should have forced a two (2) digit day (zero filled) and a THREE (3) digit month (double zero-filled) to make it clear in any language. Maybe one day. For now, Happy New Year, 2018!

Consider explicit date formats:
#dd0MMyyyy 01/001/2018
#0MMddyyyy 001.01.2018
#yyyy0MMdd 2018-001-01

#1World1Date1Day ... #1个世界1日期1天
#1Monde1Date1Jour ... #1ワールド1日付1日
#1Mundo1Fecha1Día ... #1Світ1дата1день
Info@ http://ow.ly/L0lF30fVnjz
 

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.