# 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 - Show more from this author APJ Ltd http://apjuk.com

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, bellingham, waFeb 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, Monk MediaSep 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, APJ LtdSep 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, APJ LtdSep 23, 2010 Rob, I will look at this as you make a very good point.
 Paul Jansen, APJ LtdApr 13, 2011 Modified to remove a couple of bugs and to add options for total days, total whole months or total whole weeks.
 Ahmed, OmanOct 11, 2011 how to create the fornat options
 Juergen May, BristolApr 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, BristolApr 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, APJ LtdApr 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, PlymouthAug 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, PlymouthAug 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, APJ LtdAug 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.

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

# Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: