Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
Training
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


Privacy Policy



FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

 

 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

TextToDate ( AnyText )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.4  (32 votes)
  Discuss this Custom Function

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

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

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


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Description:

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)

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

This is my Custom Function and I want to edit it

Discuss:

5 most recent comments | Show all 9 comments

Hi Peter:

Thanks for the suggestion - I incorporated it into the final version.

Doug Staubach, Denver, Colorado, USA
March 28, 2015 7:57pm

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! :-)

Gary Amstutz, Fort Wayne, IN
November 12, 2015 12:23pm

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!

Doug Staubach, Denver, CO USA
November 12, 2015 12:49pm

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?

Michael Nord, Malmö, Sweden
July 21, 2016 7:29am

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

Jeff Hurst, Canada
January 01, 2018 12:35pm

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Your Name:
City/Location:
Comment:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 9 + 9 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. HexUUIDToNum_cf (_uuid)
  (Sat, Jun 16, 1:55pm)
2. httpResponseCode ( responseHeaders )
  (Tue, Jun 12, 10:40pm)
3. FindDuplicateCharacters ( string )
  (Fri, Jun 08, 5:01pm)
4. FieldRepetitionLast ( field ; maxRepetition )
  (Wed, Jun 06, 6:05pm)
5. CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )
  (Mon, May 28, 8:23am)
6. JSONArrayLength ( JSONArrayStr )
  (Fri, May 25, 7:46am)
7. interpolation (y1; y2; y3; x1; x2; x3; x1y1; x2y1; x1y2; x2y2; rnd)
  (Sat, May 19, 3:55am)
8. @JSONFormatElements ( json )
  (Tue, May 15, 3:31am)

RSS Feed of Custom Functions