ApproxDateRange ( date1 ; date2 )

Evaluates two dates and returns a text string that describes the difference between them to an approximately half-month precision. It doesn't matter which date is more recent.

Average rating: 4.1 (35 votes) Log in to vote

Fritz Mills
Media LightBridge, LLC
http://www.medialightbridge.com/

Sample input:

"12/6/1954" ; "4/18/1952"

Sample output:

2 years, 8-1/2 months.

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

// Note: it doesn't matter which date is the older one

Let ( [ oldDate = Case ( date2 ≥ date1 ; date1 ; date2 ) ; 
          newDate = Case ( date2 ≥ date1 ; date2 ; date1 ) ; 
          fullYr = Case ( Month ( newDate ) > Month ( oldDate ) or ( Month ( newDate ) = Month ( oldDate ) and Day ( newDate ) ≥ Day ( oldDate ) ) ; 
                                 0 ; 
                                 1 
                               ) ; 
          ident = Case ( oldDate = newDate ; 1 ; 0 ) ; 
          yrs = Year ( newDate ) - Year ( oldDate ) - fullYr ; 
          mths = Case ( fullYr = 1 ; 
                                Month ( newDate ) + 12 - Month ( oldDate ) ; 
                                Month ( newDate ) - Month ( oldDate )
                               ) ; 
          apDays = Case ( Day ( newDate ) < Day ( oldDate ) ; 
                                    Day ( newDate ) + 30 ; 
                                    Day ( newDate ) 
                                  ) - Day ( oldDate ) ; 
          mthDaysA = If ( mths > 0 and apDays > 14 ; 
                                 1 ; 
                                 0 
                               ) ;
          mthDaysO = If ( mths > 0 or apDays > 14 ; 
                                 1 ; 
                                 0 
                               )
        ] ; 
        Case ( ident = 1 ; 
                  "Dates are the same." ; 
                  newDate - oldDate < 11 ; 
                  "Less than half a month." ; 
                  newDate - oldDate > 10 and newDate - oldDate < 25 ; 
                  "About half a month." ; 
                  newDate - oldDate > 25 and newDate - oldDate < 38 ; 
                  "About a month." ; 
                  yrs & " year" & Case ( yrs > 1 ; "s" ; "" ) & 
                  Case ( mthDaysO = 1 ; 
                            ", " & Case ( mths > 0 ; mths ; "" ) & Case ( apDays > 14 ; "–1/2" ; "" ) & " month" & Case ( mthDaysA = 1 ; "s" ; "" ) & "." ; 
                            ""
                           ) 
                 ) 
      )

This function evaluates two dates and returns a text string that describes the difference between them to an approximately half-month precision. It is designed to be used when a "conversational" date range description is most appropriate, such as when describing the difference in age between two persons of similar age, or the age of a very young child. It doesn't matter which date parameter is the more recent date.

Comments

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

ApproxDateRange ( date1 ; date2 )

Comments

Support this website.

Support this website.