Newest functions Upload a function Leave a tip My account

There are 2,568 functions in this library.

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

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

/* Name: DateDifference Parameters: Date1 Date2 Format Result Returned Depending On 'Format' Specified: Y 20 M 5 D 7 Full 20 Years, 5 Months, And 7 Days Short 20 Y, 5 M 2018/08/13 Updated to fix a couple of output bugs 2018/04/27 Added suggested enhancement to produce the correct pluralisation for "long" and "longWeeks" */ Let ( [ // Used for debugging in Data viewer //date1 = DateDifference::StartDate ; //date2 = DateDifference::EndDate ; //format = "shortweeks" ; // if date2 is empty set as current date date2 = If( IsEmpty( date2 ) ; Get(CurrentDate) ; date2 ) ; // swap dates if date2 before date1 d1 = Case( date1 > date2 ; date2 ; date1 ) ; d2 = Case( date1 > date2 ; date1 ; date2 ) ; // earlier date but in year of later date d1a = Date( Month( d1 ) ; Day( d1 ) ; Year( d2 )) ; // basic Year, Month and Day calcs y = Year(d2 ) - Year( d1 ) - ( d2 < d1a ) ; m = Mod ( Month ( d2 ) - Month ( d1 ) - ( Day ( d2 ) < Day ( d1 ) ) ; 12 ) ; d = d2 - Date( Month( d2 ) - (Day( d1 ) > Day( d2 )) ; Day( d1 ) ; Year( d2 )); w = Int( d / 7 ) ; // total counts mt = m + ( 12 * y ) ; //dt = Date( Month(d2) ; Day(d2) ; Year(d2)) - Date( Month(d1) ; Day(d1) ; Year(d1)) ; dt = d2 - d1 ; wt = Int( dt/7 ) ; dw = Mod( d ; 7 ) ; // set text variables - Added by Juergen May 2016-04-19 yText = Case ( y = 0 ; "" ; y = 1 ; y & " year" ; y & " years" ) ; mText = Case ( m = 0 ; "" ; m = 1 ; m & " month" ; m & " months" ) ; dText = Case ( d = 0 ; "" ; d = 1 ; d & " day" ; d & " days" ) ; wText = Case ( w = 0 ; "" ; w = 1 ; w & " week" ; w & " weeks" ) ; dwText = Case ( dw = 0 ; "" ; dw = 1 ; dw & " day" ; dw & " days" ) ; longText = Substitute ( List ( yText ; mText ; dText ) ; ¶ ; ", " ) ; longweeksText = Substitute ( List ( yText ; mText ; wText ; dwText ) ; ¶ ; ", " ) ]; // output determined by format parameter Case( format = "long" ; longText ; format = "longweeks" ; longweeksText; format = "short" ; y & " Y " & m & " M " & d & " D" ; format = "shortweeks" ; y & " Y " & m & " M " & w & " W " & dw & " D" ; format = "y" ; y ; format = "m" ; m ; //remainder months after yearsaccounted for format = "mt" ; mt ; // Total whole months format = "d" ; d ; //remainder days if not using weeks format = "dt" ; dt ; //Total days format = "w" ; w ; format = "wt" ; wt ; // Total whole weeks format = "dw" ; dw //remainder days if using weeks ) ) Click to copy

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

		David Schellenberg Jul 7, 2021
thank you!

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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 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: