Brian Dunning's FileMaker Custom Functions

Age ( Birth ; theDate ; Format )

Calculates Age in three formats

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

Geoff Wells   Geoff Wells
DataIsland Software LLC
http://www.dataisland.com

Share on Facebook Share on Twitter

  Sample input:
Age ( 5/14/1946; 5/11/2004; 3 )
  Sample output:
57 years and 11 months, and 27 days

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

The Age function will calculate a persons age, on any date, in three different formats -

1 / Years
2 / Years and Days
3 / Years, Months and Days.

 

Comments

william royster   william royster, ohio
Mar 26, 2009
Can you find it.I hop you cn find it in a good.
 
Alexis Gehrt   Alexis Gehrt, Database Designs
Nov 30, 2009
Just one little addition to the leap year factor that might be easily forgotten...
The year 2000 is not a leap year, because it's divisible by 400.

Let ( leapFactor = If ( Mod ( Year ( theDate ) ; 4 ) = 0 AND Mod ( Year ( theDate ) ; 400 ) ≠ 0 ; 1 ; 0 );
 
Rob Woof   Rob Woof, Sydney/Australia
Aug 8, 2010
Sorry, Alexis, but check your calendar again. 2000 WAS a leap year, because it's divisible by 400. The 'century' years are normally not leap years (1800, 1900), except when divisible by 400. So the calc for leapFactor should really be:

leapFactor = Case ( Mod ( Year ( theDate ) ; 400 ) = 0 ; 1 ;
// 'century' years divisible by 400 are leap years
Mod ( Year ( theDate ) ; 100 ) = 0 ; 0 ;
// 'century' years not divisible by 400 are not leap years
Mod ( Year ( theDate ) ; 4 ) = 0 ; 1 ;
// years divisible by 4 are leap years
0 )
 
Rob Woof   Rob Woof, Sydney/Australia
Aug 11, 2010
There's another way that bypasses the Case statement.

leapFactor = If ( Mod ( Year ( theDate ) ; 400 ) = 0 ; 1 ; 0 ) // 'century' years divisible by 400 are leap years
- If ( Mod ( Year ( theDate ) ; 100 ) = 0 ; 1 ; 0 ) // 'century' years not divisible by 400 are not leap years
+ If ( Mod ( Year ( theDate ) ; 4 ) = 0 ; 1 ; 0 ) // years divisible by 4 are leap years

A year that is divisible by 400 (e.g. 1600, 2000) will evaluate to 1 - 1 + 1 = 1.
A year that is divisible by 100 but not by 400 (e.g. 1300, 1900) will evaluate to 1 - 1 + 0 = 0.
A year that is divisible by 4 but not by 100 (e.g. 1972, 1988) will evaluate to 1 - 0 + 0 = 1.
Any other year (i.e. not a leap year) will evaluate to 0 - 0 + 0 = 0.

Just another way to skin this particular cat.
 
Daniel A. Shockley   Daniel A. Shockley, New York, NY
May 4, 2011
No need to do this at all: FileMaker will handle leap years itself if you use it's own date comparisons.
Go look at http://www.briandunning.com/cf/518

That one lets you pick a comparison date (just use Get( CurrentDate ) for the second parameter if you want current age), handles negative age comparisons, and returns multiple formats.

Also, it has the decided advantage of actually returning correct values, even when dealing with leap years and on the actual birthday anniversary because it uses FileMaker's own date comparisons to get it right.
 
David   David
Dec 10, 2013
Doesn't seem to work for Leap Years. Try 2008 and Feb28th then Feb29th then Mar1. It skips a date.
 
Ukyo Matsushita   Ukyo Matsushita, Tokyo/Japan
Sep 15, 2016
To be perfect, we also need to take in account the "combination" of the 4, 100 and 400.

Let (

leapFactor =

Case (

// Divisible by 4
// not by 100 and 400, such as 2012, 2016
Mod ( Year ( theDate ) ; 4 ) = 0 and
Mod ( Year ( theDate ) ; 100 ) ≠ 0 and
Mod ( Year ( theDate ) ; 400 ) ≠ 0 ;
1 ; // Leap Year

// Divisible by 4 and 100
// not by 400, such as 2200, 2300, 2500, 2600 are common years
Mod ( Year ( theDate ) ; 4 ) = 0 and
Mod ( Year ( theDate ) ; 100 ) = 0 and
Mod ( Year ( theDate ) ; 400 ) ≠ 0 ;
0 ; // Common Year

// Divisible by 4 and 100 and 400
// such as 1600, 2000, 2400 are leap years
Mod ( Year ( theDate ) ; 4 ) = 0 and
Mod ( Year ( theDate ) ; 100 ) = 0 and
Mod ( Year ( theDate ) ; 400 ) = 0 ;
1 ; // Leap Year

0 // else are Common Years

)
) ;
 
Alexis Gehrt   Alexis Gehrt, Database Designs
Jun 13, 2018
Hi everyone - maybe someone is still reading this... Personally and probably many other have used this function for years...

Here's another issue with the leap years... even in the current version.

Take: Birth 7th of June 2012 and today a June 7th 2018.

In this exception it comes up with 5 Years, 364 Days - while apparently the child is 6 years old. Same is true for June 7th 2016.

Since I didn't want to look too deeply into the formulas.. Maybe someone has a solution....
 
Alexis Gehrt   Alexis Gehrt, Database Designs
Aug 8, 2018
After thinking about it... The Leap year does not so much play a role.. Thus, if you build it into the calculation. It goes wrong by one day and on their Birthday, the person ist only 5 year 11 Month an 30 days old. Of course the leap year plays a role for the current year... Then per person is one day older.. But if the person is born in a leap year (not particularly the 29th of February) it does not so much play a role.. "People" say... the guy is 17 year old and x-days... therefore the birth year is not important...

Since I can only post 1500 Chars I have to split it into two entries
 
Part one of two   Part one of two, Alexis Gehrt
Aug 8, 2018
/* This is a Custom Function version of calculations provided in "Advanced FileMaker Pro 5.5" by Chris Moyer and Bob Bowers. This formula differs slightly from the ones in the book which do not account for leap years.



25.7.2018: Alexis Gehrt: Fix für die Falschberechnung, wenn das Geburtsdatum in einem Schaltjahr lag. Dann stimmte alles um 1 Tag nicht.



AlterBerechnen_cf



Input:

Format

Birth

TheDate



Format 1 = Jahre

Format 2 = Jahre und Tage

Format 3 = Jahre, Monate und Tage

Format 4 = J, M - nur Abkürzungen



*/



Let ( [

leapFactorHeute = If(Mod(Year ( TheDate );400) = 0; 1; If(Mod(Year ( TheDate );100)=0; 0; If(Mod(Year ( TheDate );4) = 0; 1; 0)));

leapFactorBirth = If(Mod(Year ( Birth );400) = 0; 1; If(Mod(Year ( Birth );100)=0; 0; If(Mod(Year ( Birth );4) = 0; 1; 0)));

leapFactorHeuteRelevant = If (Month (TheDate) > 2 or (Month (TheDate) = 2 and Day (TheDate) > 28); leapFactorHeute;0) ]

;





Case (



// Alter in Jahren

Format = 1 ; Year ( TheDate ) - Year ( Birth ) - (If ((Month(TheDate) < Month(Birth)) or (Month(TheDate) = Month(Birth) and Day(TheDate) < Day(Birth)) ; 1; 0)) & " Jahre";
 
Part two of two   Part two of two, Alexis Gehrt
Aug 8, 2018
// Age in years and days

Format = 2 ; Year ( TheDate ) - Year ( Birth ) - (If ((Month(TheDate) < Month(Birth)) or (Month(TheDate) = Month(Birth) and Day(TheDate) < Day(Birth)) ; 1; 0)) & " Jahre und " &

If ( (Month (TheDate) < Month(Birth)) or (Month (TheDate) = Month(Birth) and Day (TheDate) < Day(Birth)) ;

Mod(DayOfYear ( TheDate ) + ( DayOfYear ( Date ( 12 ; 31 ; Year ( TheDate -1 ) ) - DayOfYear ( Date(Month(Birth);Day(Birth);Year(TheDate -1))))) ; 365);

DayOfYear ( TheDate ) - DayOfYear(Date(Month(Birth);Day(Birth);Year(TheDate -1)) )) & " Tage" ;


// Age in years, months and days

Format = 3 ; Year ( TheDate ) - Year ( Birth ) - (If ((Month(TheDate) < Month(Birth)) or (Month(TheDate) = Month(Birth) and Day(TheDate) < Day(Birth)) ; 1; 0)) & " Jahre und " & Mod ( Month ( TheDate ) - Month ( Birth ) + 12 - (Day ( TheDate ) < Day ( Birth ) ) ; 12 ) & " Monate und " & (TheDate - Date ( Month ( TheDate ) - (Day ( TheDate ) < Day ( Birth ) ) ; Day ( Birth ) ; Year ( TheDate ) ) ) & " Tage" ;



// Age in years, months

Format = 4 ; Year ( TheDate ) - Year ( Birth ) - (If ((Month(TheDate) < Month(Birth)) or (Month(TheDate) = Month(Birth) and Day(TheDate) < Day(Birth)) ; 1; 0)) & "J " & Mod ( Month ( TheDate ) - Month ( Birth ) + 12 - (Day ( TheDate ) < Day ( Birth ) ) ; 12 ) & "M"

)

)
 
Douglas Alder   Douglas Alder, HomeBase Software
Nov 3, 2018
I added another option to show years and months only. I moved 3 to 4 and set option 3 to be this.

// Age in years and months
Format = 3 ; Year ( theDate ) - Year ( Birth ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( Birth ) ) & " years and " & Mod ( Month ( theDate ) - Month ( Birth ) + 12 - (Day ( theDate ) < Day ( Birth ) ) ; 12 ) & " months";
 

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: