Format_Expr ( ExpirationDate )

Formats a credit card expiration date, returning it in the form "MM/YY"

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

Kevin Bache
None
www.none.com

Sample input:

Format_Expr ( 5/5 )
Format_Expr ( 88 )
Format_Expr ( 123 )
Format_Expr ( 3.2006 )
Format_Expr ( 1307 )

Sample output:

05/05
08/08
12/03
03/06
Error

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

/*
------------------------------
Format_Expr version 1.0
------------------------------
Formats a credit card expiration date, returning it in the form: "MM/YY".

Dates < 1970 or > 2069 will return an error.

You can edit the marked line at the bottom of the function to change output format.
You can edit the marked error_message variable to change the value returned if there's an error.

Accepts dates in the form:
M/Y
M/YY
M/YYYY

MM/Y
MM/YY
MM/YYYY 

MY
MMY
MMYY
MYYYY
MMYYYY

"." (period) and "-" may be substituted for "/"

This function does not accept dates in the format "MYY" since there are cases where you cannot distinguish MYY and MMY (112, 102, etc.)

All characters but 1234567890, "/", "-" and "."  are ignored.

trendymoniker@gmail.com for bug reports, requests, or questions.

Feel free to distribute and adapt as you wish.
*/

Let (
         [ 
           // EDIT THIS LINE TO CHANGE THE ERROR MESSAGE
           error_message = "Error" ;

           date_trimmed = GetAsText ( Filter ( Substitute ( ExpirationDate ; ["."; "/"]; ["-"; "/"] ); "1234567890/" ) );

           cc_year_raw = Case ( 
                                          PatternCount (  date_trimmed ; "/") = 1 ;
                                          Right ( date_trimmed ; 
                                                      Length ( date_trimmed ) -  Position ( date_trimmed ; "/" ; 0 ; 1  )
                                                    ) ;
    
                                          PatternCount ( date_trimmed; "/") = 0 ;
                                          Case ( 
                                                     Length ( date_trimmed ) = 2 ;
                                                     Right ( date_trimmed ; 1 ) ;

                                                     Length ( date_trimmed ) = 3 ;
                                                     Right ( date_trimmed ; 1 ) ;

                                                     Length ( date_trimmed ) = 4 ;
                                                     Right ( date_trimmed ; 2 ) ;

                                                     Length ( date_trimmed ) = 5 ;
                                                     Right ( date_trimmed ; 4 ) ;

                                                     Length ( date_trimmed ) = 6 ;
                                                     Right ( date_trimmed ; 4 );
                                                    ) 
                                       ) ;


           cc_year = Case (
                                  Length ( cc_year_raw ) = 4 ;
                                  cc_year_raw ;

                                   Length ( cc_year_raw ) = 2 ;
                                        Case ( 
                                                    Left ( cc_year_raw  ; 1 ) = "7" ;
                                                    "19" & cc_year_raw ;

                                                    Left ( cc_year_raw ; 1 ) = "8" ;
                                                    "19" & cc_year_raw ;
                                                         
                                                    Left ( cc_year_raw ; 1 ) = "9" ;
                                                    "19" & cc_year_raw ;

                                                    // Else
                                                    "20" & cc_year_raw 
                                                 ) ;

                                   Length ( cc_year_raw ) = 1 ;
                                   "200" & cc_year_raw 
                                  ) ;


          cc_month_raw = Case ( 

                                            PatternCount ( date_trimmed ; "/" ) > 1 ; 
                                            "Invalid Expiration Date" ;

                                            PatternCount ( date_trimmed ; "/" ) = 1 ;
                                            GetAsNumber (
                                                                     Left ( 
                                                                               date_trimmed ; 
                                                                               Position ( date_trimmed ; "/" ; 0 ; 1 ) - 1 
                                                                             )
                                                                    ) ;

                                            PatternCount ( date_trimmed ; "/" ) = 0 ;
                                            Case (
                                                       Length ( date_trimmed ) = 2 ;
                                                       Left ( date_trimmed ; 1 ) ;

                                                       Length ( date_trimmed ) = 3 ;
                                                       Left ( date_trimmed ; 2 ) ;

                                                       Length ( date_trimmed ) = 4 ;
                                                       Left ( date_trimmed ; 2 ) ;

                                                       Length ( date_trimmed ) = 5 ;
                                                       Left ( date_trimmed ; 1 ) ;

                                                       Length ( date_trimmed ) = 6 ;
                                                       Left ( date_trimmed ; 2 );
                                                      )

                                          ) ; // close case

          cc_month = Case ( 
                                    Length ( cc_month_raw ) = 1 ;
                                    "0" & cc_month_raw ;

                                    Length ( cc_month_raw ) = 2 ;
                                    cc_month_raw ;
                                  ) ;

           error_flag = Case (
                                           PatternCount ( date_trimmed ; "/") > 1 ;
                                           1;

                                           PatternCount ( date_trimmed; "/") = 0 and Length ( date_trimmed ) < 2 ;
                                           1;

                                           PatternCount ( date_trimmed; "/") = 0 and Length ( date_trimmed ) > 6 ;
                                           1;

                                           Length ( date_trimmed ) > 7 ; 
                                           1;

                                           Length ( cc_year_raw ) ≠ 4 and Length ( cc_year_raw ) ≠ 2 and Length ( cc_year_raw ) ≠ 1 ;
                                           1;

                                           Length ( cc_month_raw ) ≠ 2 and Length ( cc_month_raw ) ≠ 1 ;
                                           1;

                                           GetAsNumber ( cc_year ) < 1970 ;
                                           1;

                                           GetAsNumber ( cc_year ) > 2069 ;
                                           1;

                                           GetAsNumber ( cc_month ) < 1 ;
                                           1;

                                           GetAsNumber ( cc_month ) > 12 ;
                                           1;
										  
		           // Else
	  	           0
                                         ) 


         ] ;

             Case ( 

                        IsEmpty ( date_trimmed ) ; "" ;

                        error_flag ; error_message ;

                        // EDIT THIS LINE TO CHANGE THE OUTPUT FORMAT
                        cc_month & "/" & Right ( cc_year ; 2 )
//                        cc_month & "/" & cc_year
//                        cc_month & Right ( cc_year ; 2 )
//                        cc_month & cc_year

                      ) 

       ) // end let

------------------------------
Format_Expr version 1.0
------------------------------
Formats a credit card expiration date, returning it in the form: "MM/YY".

Dates < 1970 or > 2069 will return an error.

You can edit the marked line at the bottom of the function to change output format.
You can edit the marked error_message variable to change the value returned if there's an error.

Accepts dates in the form:
M/Y
M/YY
M/YYYY

MM/Y
MM/YY
MM/YYYY

MY
MMY
MMYY
MYYYY
MMYYYY

"." (period) and "-" may be substituted for "/"

This function does not accept dates in the format "MYY" since there are cases where you cannot distinguish MYY and MMY (112, 102, etc.)

All characters but 1234567890, "/", "-" and "." are ignored.

trendymoniker@gmail.com for bug reports, requests, or questions.

Feel free to distribute and adapt as you wish.

Comments

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

Format_Expr ( ExpirationDate )

Comments

Support this website.

Support this website.