FMDiff™

This site is W3C compliant:
Valid XHTML - Valid CSS
Last modified June 02 2009, 00:00:00 CEST.

Calculating the Age (with and without time)

What is the most simple calculation?

Not one month passes without this question being posted to a FileMaker® Pro related list.

I've created a custom function that is neat and clean:

Function Name and Parameters:
age ( date1 ; date2 )

Function Definition:

Let ( [
    neg = Case ( date1 > date2; -1; 1 );
    d1 = Case ( neg < 0; date2; date1 );
    d2 = Case ( neg < 0; date1; date2 );

    d = Mod ( Day ( d2 ) - Day ( d1 ); Day ( 
        Date ( Month ( d1 ) + 1; 0; Year ( d1 ) ) ) );
        
    m = Mod ( Month ( d2 ) - Month ( d1 ) -
        ( Day ( d2 ) < Day ( d1 ) ); 12 );
        
    y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -
        ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )
  ];
    y * neg & ¶ & 
    m * neg & ¶ &  
    d * neg & ¶ &
    y & " years, " & m & " months, and " & d & " days" & ¶
)

Result returned:
20
5
7
20 years, 5 months, and 7 days

This calculation takes into account the different number of days in a month as well as leap years and negative distances. The result is provided as a line separated list containing Years, Months, Days, and a compound string like "20 years, 5 months, and 7 days". To access these results use LeftValues (), MiddleValues (), and RightValues () respectively.

This formula is meant as a replacement for the one given at FileMaker Custom Help

You may download the sample file agecalc.fp7.zip (10 kByte) for exploration. Information and files provided "AS IS" without any guarantee or liability.

How about including the Time?

This functions returns the difference between two time stamps:

Function Name and Parameters:
ageTime ( timestamp1 ; timestamp2 )

Function Definition:

// © 2007 Winfried Huslik, www.fmdiff.com

Let ( [
   neg = Case ( timestamp1 > timestamp2; -1; 1 );
    d1 = Case ( neg < 0; timestamp2; timestamp1 );
    d2 = Case ( neg < 0; timestamp1; timestamp2 );

     s = Mod  ( Seconds ( d2 ) - Seconds ( d1 );    60 );
    d1 = d1 + ( Seconds ( d2 ) < Seconds ( d1 ) ) * 60;			// correct minutes

    mi = Mod  ( Minute  ( d2 ) - Minute  ( d1 );    60 );
    d1 = d1 + ( Minute  ( d2 ) < Minute  ( d1 ) ) * 3600;		// correct hours

     h = Mod  ( Hour    ( d2 ) - Hour    ( d1 );    24 );
    d1 = d1 + ( Hour    ( d2 ) < Hour    ( d1 ) ) * 86400;		// correct days

    maxDaysMonth = Day ( Date ( Month ( d1 ) + 1; 0; Year ( d1 ) ) );
    
     d = Mod  ( Day     ( d2 ) - Day     ( d1 );    maxDaysMonth );
     m = Mod  ( Month   ( d2 ) - Month   ( d1 ) - ( Day ( d2 ) < Day ( d1 ) ); 12 );
     y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) - ( Day ( d2 ) < Day ( d1 ) ) ) < Month ( d1 ) )
  ];
  y * neg & ¶ & 
  m * neg & ¶ &  
  d * neg & ¶ &
  h * neg & ¶ &
 mi * neg & ¶ &
  s * neg & ¶
)

Result returned:
20
5
7
23
59
59

This calculation takes into account the different number of days in a month as well as leap years and negative distances and the time. The result is provided as a line separated list containing Years, Months, Days, Hours, Minutes, Seconds. To access these results you may use MiddleValues (x; 1), where x is (1) Years, (2) Months, (3) Days, (4) Hours, (5) Minutes, and (6) Seconds. For other requirements you may adjust the result format as you like.

You may download the sample file that contains both Custom Functions agecalc2.fp7.zip (10 kByte) for exploration.

Have fun!

Suggestions, opinions, experience reports and other hints are always welcome via our Contacts page.