# 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 APJ Ltd http://apjuk.com |

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, 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, 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, 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, APJ Ltd Sep 23, 2010 |
||

Rob, I will look at this as you make a very good point. |
||

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, Oman Oct 11, 2011 |
||

how to create the fornat options | ||

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, 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, 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, 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, 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, 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. |
||

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