Converting textual dates in FileMaker

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!

Leave a Reply