Brian Dunning's FileMaker Custom Functions

age ( date1 ; date2 )

Calculates the number of years, months, and days between any two dates

  Average rating: 4.0 (87 votes) Log in to vote

Winfried Huslik   Winfried Huslik
Huslik Verlag GmbH
http://fmdiff.com

Share on Facebook Share on Twitter

  Sample input:
age ("7/27/1980" ; "7/26/2006" )
  Sample output:
25
10
30
25 years, 10 months, and 30 days

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

This calculation takes into account different 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. To access these results use LeftValues (), MiddleValues (), and RightValues () respectively. Use at your own risk.

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

Address comments to agecalc@fmdiff.com.
Download an example file at http://fmdiff.com/fm/agecalc.html.

 

Comments

Darren   Darren, UK
Jul 13, 2010
Don't know why the vote is so low on this function?

It's the only age function that calc's the date correctly.

Thank you
 
Chaim Bacon   Chaim Bacon, London, UK
Apr 6, 2011
This function is accurate. I had previously used the custom function at www.briandunning.com/cf/57, but that produced errors ON THE ACTUAL BIRTHDAY date.
 
Garry Hansford   Garry Hansford, Toowoomba, AUS
Apr 9, 2011
Works for me!
 
Rick   Rick, Emerson, NJ USA
Oct 30, 2011
Works great, fixed issues I had with other functions. I agree with the others that seems to be the best age calc function available.
 
James Hea   James Hea
Feb 27, 2012
I tested this against another custom function that also failed on the birthday. This one works!
 
HazMatt   HazMatt, Blaine, MN USA
Mar 29, 2013
Function seems to work dandy! One minor quibble is that the fourth value output always assumes a plural value (i.e. "1 Days"). To fix that, you can change the last output line to:

y & " year" & If(y = 1; ""; "s") & ", " & m & " month" & If(m = 1; ""; "s") & ", and " & d & " day" & If(d = 1; ""; "s") & ¶

Regardless, I rated this function 5 / 5.
 
Ganesh   Ganesh, Carmel, CA
May 6, 2013
Awesome Calculation. You can also try
http://help.filemaker.com/app/answers/detail/a_id/5532/kw/todays%20date%20function/session/L3RpbWUvMTM2Nzg5OTc1NC9zaWQvT3h5cl95cGw%3D

Full Age
FullAge (calculation, text result) =
GetAsText ( Year ( Get ( CurrentDate ) ) - Year ( Birthdate ) - If ( Get ( CurrentDate ) < Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Get ( CurrentDate ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( CurrentDate ) ) - Month ( Birthdate ) + 12 - If ( Day ( Get ( CurrentDate ) ) < Day ( Birthdate ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( CurrentDate ) ) - Day ( Birthdate ) + If ( Day ( Get ( CurrentDate ) ) ≥ Day ( Birthdate ); 0 ; If ( Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) < Day ( Birthdate ) ; Day ( Birthdate ) ; Day ( Get ( CurrentDate ) - Day ( Get ( CurrentDate ) ) ) ) ) ) & " Days "
 
Ganesh   Ganesh, Carmel, CA
May 31, 2013
Just saw this on a post at fmlayouts.com (unrelated to the age calculation issue) but the post by loSTaNT had a file that had an awesome age calculation field that works and is much simpler as well. You can download the file at: http://fmlayoutmode.com/wp/?p=1356

and the referenced calculation field is:
Let ( [
@cd = Get ( CurrentDate )
; @cdConverted = Year ( @cd ) + Month ( @cd ) / 100 + Day ( @cd ) / 10000
; @bdConverted = Year (Birthdate ) + Month (Birthdate ) / 100 + Day (Birthdate ) / 10000
] ;

Int ( @cdConverted - @bdConverted )

)
 
KevinSmith   KevinSmith
Jan 22, 2014
It works and I'm happy with the function.

a) Accuracy - I've tested it against "FullAge (calculation, text result)" mentioned in this discussion by Ganesh on May 6 2013. The two agree most of the time. Sometimes there's a day's difference. I assume that's because there's room for defining a month as 30/31 days.

b) Absolute Value - The "FullAge (calculation, text result)" will give a negative number if you swap the dates around. This function age ( date1 ; date2 ) always assumes you want a positive figure.

c) Parameters - I like that it passes back info as 4 separate parameters : Y,M,D,Sentence

d) Singular/Plural - Make this adjustment to the last part of the calculation. It ensures that if there is one day/month/year that system returns "month" instead of "months":
y * neg & ¶ &
m * neg & ¶ &
d * neg & ¶ &
y & If (y = 1; " year, " ;" years, " )&
m & If (m=1; " month & ";" months & " )&
d & If (d=1;" day";" days" ) & ¶
 
Dave   Dave, Birmingham, AL
Jun 18, 2015
I brought it over as is, and then made a duplicate, named it age_abbreviated, and changed the output to:

y * neg & " Y, " &
m * neg & " M, " &
d * neg & " D"

So output goes to something like "34 Y, 3 M, 13 D" which fits nicer into the field I set up.

Very appreciative of this function!
 
M Boucher   M Boucher, France
Mar 20, 2018
hi,

I try all of them but have a problem with the February 29 and of course, at the end there is a problem
 

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.