Newest functions Upload a function Leave a tip My account

There are 2,520 functions in this library.

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 - Show more from this author https://www.linkedin.com/in/dougstaubach/

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

// Created Feb 6, 2014 by Doug Staubach -- Last Modified Nov 12, 2015 (v.1.4) // Accepts just about any text string that looks like a date and attempts to // convert it into a valid date for FileMaker. Works will when combined with // a script trigger for "OnObjectValidate" (avoids silly validation messages). // Automatically recognizes US, International, and ISO date formats. // Will ignore any extra text appearing after date (like time). Works best if // the date is split by delimiters, but will try to process without delimiters. // If only month and date are provided, the function will auto-add the current year. // Works as a stand-alone function and with validation scripts (find and browse). // If the text cannot be converted to a valid date, the function does nothing. Let ([ MYTEXT = Trim ( Substitute ( GetAsText ( Trim ( ANYTEXT ) ) ; [ "Monday" ; "" ] ; [ "Tuesday" ; "" ] ; [ "Wednesday" ; "" ] ; [ "Thursday" ; "" ] ; [ "Friday" ; "" ] ; [ "Saturday" ; "" ] ; [ "Sunday" ; "" ] ; [ "-" ; " " ] ; [ "/" ; " " ] ; [ "." ; " " ] ; [ "," ; " " ] ; [ " " ; " " ] ) ); // strips out common date delimiters NUMARGS = WordCount ( MYTEXT ); // Get first part (and see if if contains subparts) PART1 = LeftWords ( MYTEXT ; 1 ); SUBTEXT = Filter ( Lower ( PART1 ) ; "abcdefghijklmnopqrstuvwxyz" ); SUBNUM = Filter ( PART1 ; "0123456789" ); MYTEXT = Case ( Length ( SUBTEXT ) = 0 or Length ( SUBNUM ) = 0 ; MYTEXT; // If the user typed an ISO date without any delimiters (example: 20120307) split this into 3 parts Length ( SUBTEXT ) = 0 and Length ( SUBNUM ) = 8 ; Left ( SUBNUM ; 4) & " " & Middle ( SUBNUM ; 5 ; 2) & " " & Right ( SUBNUM ; 2) & RightWords ( MYTEXT ; NUMARGS - 1 ); // If the user typed a text month and numeric date with no delimiters (example: Feb6 or 23Feb) split this part into 2 or 3 parts Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 1 ; SUBTEXT & " " & SUBNUM & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 2 ; SUBTEXT & " " & SUBNUM & RightWords ( MYTEXT ; NUMARGS - 1 ); // If the user typed a text month and numeric date and numeric year with no delimiters (example: Feb613 or 23Feb13) split this part into 3 parts Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 3 ; SUBTEXT & " " & Left ( SUBNUM ; 1 ) & " " & Right (SUBNUM ; 2) & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 4 ; SUBTEXT & " " & Left ( SUBNUM ; 2 ) & " " & Right (SUBNUM ; 2) & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 5 ; SUBTEXT & " " & Left ( SUBNUM ; 1 ) & " " & Right (SUBNUM ; 4) & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 6 ; SUBTEXT & " " & Left ( SUBNUM ; 2 ) & " " & Right (SUBNUM ; 4) & RightWords ( MYTEXT ; NUMARGS - 1 ) ); // end Case NUMARGS = WordCount ( MYTEXT ); // count again PART1 = LeftWords ( MYTEXT ; 1 ); // get first part again PART1HASTEXT = Length ( SUBTEXT ); // Get second part (and see if it contains subparts) PART2 = MiddleWords ( MYTEXT ; 2 ; 1 ); SUBTEXT = Filter ( Lower ( PART2 ) ; "abcdefghijklmnopqrstuvwxyz" ); SUBNUM = Filter ( PART2 ; "0123456789" ); MYTEXT = Case ( Length ( SUBTEXT ) = 0 or Length ( SUBNUM ) = 0 ; MYTEXT; // If the user typed a text month and numeric date with no delimiters (example: Feb6 or 23Feb) split this part into 2 or 3 parts Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 1 ; SUBTEXT & " " & SUBNUM & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 2 ; SUBTEXT & " " & SUBNUM & RightWords ( MYTEXT ; NUMARGS - 1 ); // If the user typed a text month and numeric date and numeric year with no delimiters (example: Feb613 or 23Feb13) split this part into 3 parts Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 3 ; SUBTEXT & " " & Left ( SUBNUM ; 1 ) & " " & Right (SUBNUM ; 2) & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 4 ; SUBTEXT & " " & Left ( SUBNUM ; 2 ) & " " & Right (SUBNUM ; 2) & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 5 ; SUBTEXT & " " & Left ( SUBNUM ; 1 ) & " " & Right (SUBNUM ; 4) & RightWords ( MYTEXT ; NUMARGS - 1 ); Length ( SUBTEXT ) > 2 and Length ( SUBNUM ) = 6 ; SUBTEXT & " " & Left ( SUBNUM ; 2 ) & " " & Right (SUBNUM ; 4) & RightWords ( MYTEXT ; NUMARGS - 1 ) ); // end Case NUMARGS = WordCount ( MYTEXT ); // count again PART2 = MiddleWords ( MYTEXT ; 2 ; 1 ); // get second part again PART2HASTEXT = Length ( SUBTEXT ); // Get the third part of the date if it exists -- if the user only provided 2 parts, then set this to the current year PART3 = If ( NUMARGS > 2 ; MiddleWords ( MYTEXT ; 3 ; 1 ) ; GetAsText ( Year ( Get ( CurrentDate ) ) ) ); // if the date itself gives us a clue on the format, then go with it, otherwise use system locale as default format MYFORMAT = Case ( PART1HASTEXT = 0 and Length( PART1 ) > 3 ; "ISO" ; PART1HASTEXT > 0 or GetAsNumber( PART2 ) > 12 ; "US" ; PART2HASTEXT > 0 or GetAsNumber( PART1 ) > 12; "INTL" ; If ( Get ( SystemLanguage ) ≠ "English" ; "INTL" ; "US" ) ); // end Case // Now that we have determined the format, we can change any month names into month numbers // Credit to David Head for his ideas on the month name to month number conversion // David's function is listed at https://www.briandunning.com/cf/805 PART1 = If ( PART1HASTEXT = 0 ; PART1 ; ( Position ( "xxjanfebmaraprmayjunjulaugsepoctnovdec" ; Left ( Lower ( PART1 ) ; 3 ) ; 1 ; 1 ) / 3 ) ); PART2 = If ( PART2HASTEXT = 0 ; PART2 ; ( Position ( "xxjanfebmaraprmayjunjulaugsepoctnovdec" ; Left ( Lower ( PART2 ) ; 3 ) ; 1 ; 1 ) / 3 ) ) ]; // end first half of Let Case ( MYFORMAT = "ISO" and IsValid ( GetAsDate ( PART2 & "/" & PART3 & "/" & PART1 ) ) ; GetAsDate ( PART2 & "/" & PART3 & "/" & PART1 ); MYFORMAT = "US" and IsValid ( GetAsDate ( PART1 & "/" & PART2 & "/" & PART3 ) ) ; GetAsDate ( PART1 & "/" & PART2 & "/" & PART3 ); MYFORMAT = "INTL" and IsValid ( GetAsDate ( PART2 & "/" & PART1 & "/" & PART3 ) ) ; GetAsDate ( PART2 & "/" & PART1 & "/" & PART3 ); ANYTEXT // if we can't make it look like a date, just return the original text unchanged ) // end Case ) // end second half of Let Click to copy

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 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 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 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, 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 Mar 28, 2015
Hi Peter: Thanks for the suggestion - I incorporated it into the final version.

		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 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, 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, 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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: