Brian Dunning's FileMaker Custom Functions

DateDifference ( date1 ; date2 ; format )

Calculate age ( years, months, weeks and days between 2 any two dates

  Average rating: 3.9 (42 votes) Log in to vote

Paul Jansen   Paul Jansen
APJ Ltd
http://apjuk.com

Share on Facebook Share on Twitter

  Sample input:
DateDifference ( GetAsDate("02/02/2000") ; GetAsDate("31/03/2008") ; "longweeks" )
  Sample output:
8 years, 1 month, 4 weeks, 1 day

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

This function calculates the age between two dates. The output is determined by the third parameter.

DateDifference ( GetAsDate("02/02/2000") ; GetAsDate("31/03/2008") ; Format ) will produce the following...

longweeks: 8 years, 1 month, 4 weeks, 1 day
long: 8 years, 1 month, 29 days
shortweeks: 8 Y 1 M 4 W 1 D
short: 8 Y 1 M 29 D


 

Comments

ron   ron, bellingham, wa
Feb 23, 2009
Nice function.
But, when date2 does NOT have a value, weird "6/0/0006 years, 8 months, ? days" shows up. How do I do a "If date2 is blank then the AgeField is blank" modification?
 
Rob   Rob, Cleveland, Ohio
Sep 23, 2010
Nice, but...

If I request a format say just "M" and then have two dates that are more than 12 months apart, I'd like to see the # of months only. eg 9/18/2009 , 9/24/2010 should give me 12 months. I get 0. It can be worked around but it would be nice in one function.
 
Paul Jansen   Paul Jansen, APJ Ltd
Sep 23, 2010
Ron,

You must have 2 dates to compare!

I have changed the function to use get(current date) if date 2 is empty
 
Paul Jansen   Paul Jansen, APJ Ltd
Sep 23, 2010
Rob,

I will look at this as you make a very good point.
 
Paul Jansen   Paul Jansen, APJ Ltd
Apr 13, 2011
Modified to remove a couple of bugs and to add options for total days, total whole months or total whole weeks.
 
Ahmed   Ahmed, Oman
Oct 11, 2011
how to create the fornat options
 
Juergen May   Juergen May, Bristol
Apr 19, 2016
Adding the following after '//total counts' provides nicer formatting for 'long' and 'longweeks' where days, weeks, months or years is 0.

// set text variables - Added by Juergen May 2016-04-19
yText = If ( y = 0 ; "" ; Case ( y = 1 ; y & " year" ; y & " year" ) ) ;
mText = If ( m = 0 ; "" ; Case ( m = 1 ; m & " month" ; m & " months" ) ) ;
dText = If ( d = 0 ; "" ; Case ( d = 1 ; d & " day" ; d & " days" ) ) ;
wText = If ( w = 0 ; "" ; Case ( w = 1 ; w & " week" ; dw & " weeks" ) ) ;
dwText = If ( dw = 0 ; "" ; Case ( dw = 1 ; dw & " day" ; dw & " days" ) ) ;
longText = Substitute ( List ( yText ; mText ; dText ) ; ¶ ; ", " ) ;
longweeksText = Substitute ( List ( yText ; mText ; wText ; dwText ) ; ¶ ; ", " )
 
Juergen May   Juergen May, Bristol
Apr 19, 2016
This line:

yText = If ( y = 0 ; "" ; Case ( y = 1 ; y & " year" ; y & " year" ) ) ;

Should read:

yText = If ( y = 0 ; "" ; Case ( y = 1 ; y & " year" ; y & " years" ) ) ;

(I missed the 's' off of 'years')
 
Paul Jansen   Paul Jansen, APJ Ltd
Apr 27, 2018
I have updated the function to include the enhancements suggested by Juergen - sorry it's taken so long to notice them!
 
Jim Randell   Jim Randell, Plymouth
Aug 13, 2018
should this:
wText = If ( w = 0 ; "" ; Case ( w = 1 ; w & " week" ; dw & " weeks" ) ) ;

be this?:
wText = If ( w = 0 ; "" ; Case ( w = 1 ; w & " week" ; w & " weeks" ) ) ;
 
Jim Randell   Jim Randell, Plymouth
Aug 13, 2018
Should this:
format = "m" ; m ; //remainder months after yearsaccounted for

Be this:
format = "m" ; If( y = 0 ; 0; m ) ; //remainder months after yearsaccounted for
 
Paul Jansen   Paul Jansen, APJ Ltd
Aug 13, 2018
Jim,

I have reviewed your comments. The first was a bug (thanks for pointing it out), but the second was not. Even with y=0, m could have values of 1 to 11.

On reviewing the code in my test file, I spotted another error in the 'shortWeeks' format!

I have also tidied up the additions that Jurgen suggested by combining the If and Case statements into a single Case statement.
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.