Newest functions Upload a function Leave a tip My account

There are 2,521 functions in this library.

GetTextAsDate ( dateTxt ; formatTxt )

Convert a date string of various formats into a FMP date value

  Average rating: 4.5 (38 votes) Log in to vote

Simon Brown beezwax datatools, inc. http://beezwax.net/

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

Let ( [ // Clean up a bit to be on safe side. $dateTrimmed = Trim (dateTxt); // Determine date format: is the GetAsDate function expecting the month or day first? //$monthFirst = If (Left (Date(1; 3; 2222); 1) = 1; 1; 0); // These two date formats have synomyms. We do the substitutions to simplify the case logic. $format = Substitute (Substitute (formatTxt; "yyyy"; "yy"); "month"; "mmm"); // Make date string into something we can parse elements out of by replaces "-", ".", and "/" with a space. $dateWords = Substitute (Substitute (Substitute ($dateTrimmed; "-"; " "); "."; " "); "/"; " ") ]; Case ( // The IF block returns true if this is a format we want to interpret. If ($format="dd-mmm-yy" or formatTxt="dd/mmm/yy" or formatTxt="dd.mmm.yy"; 1; 0); // In this instance, the month is encoded as a letter abbreviation or its full name, so we must convert the month string to its ordinal. // We also use the FMP Date function to convert the year seperately so that we can rely on FMP to convert 2-digit years appropriately. Date (GetMonthAsNumber (MiddleWords ($dateWords; 2; 1)); LeftWords ($dateWords; 1); Year (GetAsDate ("1/1/" & RightWords ($dateWords; 1)))); If ($format="dd-mm-yy" or formatTxt="dd/mm/yy" or formatTxt="dd.mm.yy"; 1; 0); Date (MiddleWords ($dateWords; 2; 1); LeftWords ($dateWords; 1); Year (GetAsDate ("1/1/" & RightWords ($dateWords; 1)))); If ($format="mm-dd-yy" or formatTxt="mm/dd/yy" or formatTxt="mm.dd.yy"; 1; 0); Date (LeftWords ($dateWords; 1); MiddleWords ($dateWords; 2; 1); Year (GetAsDate ("1/1/" & RightWords ($dateWords; 1)))); If ($format="yy-mm-dd" or formatTxt="yy/mm/dd" or formatTxt="yy.mm.dd"; 1; 0); Date (MiddleWords ($dateWords; 2; 1); RightWords ($dateWords; 1); Year (GetAsDate ("1/1/" & LeftWords ($dateWords; 1)))); If ($format="yy-dd-mm" or formatTxt="yy/dd/mm" or formatTxt="yy.dd.mm"; 1; 0); Date (RightWords ($dateWords; 1); MiddleWords ($dateWords; 2; 1); Year (GetAsDate ("1/1/" & LeftWords ($dateWords; 1)))); If ($format="mmm dd, yy" or $format="month dd, yy"; 1; 0); Date (GetMonthAsNumber (LeftWords ($dateWords; 1)); MiddleWords ($dateWords; 2; 1); Year (GetAsDate ("1/1/" & RightWords ($dateWords; 1)))); If ($format = "day, month dd, yy" or $format = "day, mmm dd, yy"; 1; 0); // We ignore the day string, and instead rely on dd. Date (GetMonthAsNumber (MiddleWords ($dateWords; 2; 1)); MiddleWords ($dateWords; 3; 1); Year (GetAsDate ("1/1/" & MiddleWords ($dateWords; 4; 1)))); $format="yymmdd"; // This format is actually always a 4-digit year (yyyy), but the earlier format subsitution clobbered the format. Date (Middle ($dateTrimmed; 5; 2); Right ($dateTrimmed; 2); Left ($dateTrimmed; 4)); // DEFAULT // We can't evaluate the format given. "invalid format: " & $format ) ) /* ---------------------------------------- # Function: Convert a text string of given format into a date value. Returns an error message as its result if an invalid format string was used. 2-digit years follow FileMaker's convention for conversion to 4-digit years. Values are NOT range checked, but this does mean you can use, eg, 13/1/2007 to get 1/1/2008. # Parameters: formatTxt: This is the date format that will be used with input string. For all of these, the year can be in either 2 or 4 digit format. The available formats are below, grouped by their related synomyms: day, mmm dd, yy Note: the day (eg "Tuesday") is ignored. You can safely pass a timestamp string (everything after year is ignored). day, mmm dd, yyyy day, month dd, yy day, month dd, yyyy dd-mm-yy dd/mm/yy dd.mm.yy dd-mm-yyyy dd/mm/yyyy dd.mm.yyyy dd-mmm-yy Note: mmm is a 3 char month abbreviation (ie, JAN), not a number dd/mmm/yy dd.mmm.yy dd-mmm-yyyy dd/mmm/yyyy dd.mmm.yyyy dd-month-yy Note: month is a month name (ie, january), not a number dd/month/yy dd.month.yy dd-month-yyyy dd/month/yyyy dd.month.yyyy mm-dd-yy mm/dd/yy mm.dd.yy mm-dd-yyyy mm/dd/yyyy mm.dd.yyyy mmm dd, yy Note: mmm=month's 3 letter abbreviation, month=month's full name mmm dd, yyyy month dd, yy month dd, yyyy yy-mm-dd yy/mm/dd yy.mm.dd yyyy-mm-dd yyyy/mm/dd yyyy.mm.dd yyyymmdd Note: the month & day must be zero filled dateTxt: The date string to be converted # Created: 2008-Dec-15 10h52 # Modified: # Author: Simon Brown ---------------------------------------- */ Click to copy

Although FileMaker's built-in GetAsDate function is somewhat forgiving with its input, this function was designed to handle those that aren't covered, with 8 date formats supported, or over 40 specific variations in all. It is not fully internationalized however, since some calculations assume dates are in the mm/dd/yyyy format.

IMPORTANT: requires a separate helper function, GetMonthAsNumber (http://www.briandunning.com/cf/981) to convert a month name to its ordinal.

 

Comments

		caitlin Jul 18, 2019
The format yyyy/mm/dd isn't included in this function, but is easily added to the section that includes "yy/mm/dd" format with no other alteration needed.

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: