If you’re working with data from outside of FileMaker, you’ll commonly have to work with dates that FileMaker’s GetAsDate function won’t understand. To handle this, I created a custom function that allows conversion of a wide variety of date formats.
Some examples of how the function works:
- GetTextAsDate (“07.19.01” ; “yy.dd.mm”)
- GetTextAsDate (“Jan 19, 2007” ; “mmm dd, yyyy”)
- GetTextAsDate (“20080616”; “yyyymmdd”)
- GetTextAsDate (“Friday, Jan 19, 2007” ; “day, month dd, yy”)
These all equate to a FileMaker date output of 1/19/2007. Read the comments at the bottom of function for some limitations and non-obvious behavior.
If you’re new to FileMaker development, you’ll need to know that FileMaker Advanced is required to create, but not use, Custom Functions.
// G e t T e x t A s D a t e
//
// Function Parameters:
// dateTxt raw date text to convert into FMP date
// formatTxt string indicating the format of the date
Let (
[
// Clean up a bit to be on safe side.
dateTrimmed = Trim (dateTxt);
// These two date formats have synomyms. We do the substitutions here 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 replacing "-", ".", and "/" with a space.
dateWords = Substitute (dateTrimmed; ["-"; " "]; ["/"; " "]; ["."; " "])
]; // Each date format will use a different expression in the case statement. 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 = 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 = 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 simon_b beezwax.net # Modified: 2009-Jan-14 13h34 simon_b beezwax.net converted nested substitutes to list form # Author: Simon Brown # Requires: GetMonthAsNumber custom function <---------------------------------------- */
The GetTextAsDate function requires a helper function, GetMonthAsNumber. This is a trivial CF used to convert an English month name into its ordinal value:
GetMonthAsNumber (monthTxt) Case ( monthTxt = "jan"; 1; monthTxt = "January"; 1; monthTxt = "feb"; 2; monthTxt = "February"; 2; monthTxt = "mar"; 3; monthTxt = "March"; 3; monthTxt = "apr"; 4; monthTxt = "April"; 4; monthTxt = "may"; 5; monthTxt = "jun"; 6; monthTxt = "June"; 6; monthTxt = "jul"; 7; monthTxt = "July"; 7; monthTxt = "aug"; 8; monthTxt = "August"; 8; monthTxt = "sep"; 9; monthTxt = "September"; 9; monthTxt = "oct"; 10; monthTxt = "October"; 10; monthTxt = "nov"; 11; monthTxt = "November"; 11; monthTxt = "dec"; 12; monthTxt = "december"; 12; // default 0 ) /* ---------------------------------------- # Function: Convert a month string, either full name or 3 char abbreviation, into its ordinal value (base 1) # Parameters: monthTxt: String to be converted # Created: 2008-Jan-15 simon_b@beezwax.net # Modified: # Author: Simon Brown ---------------------------------------- */
I hope this helps with your conversion of dates!