FileMaker TIP: Calculating Age

Calculating the age of something (or someone) is a task that I come across a lot. Here's a simple calculation that will help get you the basics:

Let ([
  today = Get ( CurrentDate ) ;
  days = today - MyDateField;
  md = Date ( 0 ; days + 1 ; Year ( MyDateField) ) ;
  y = Int ( days / 365 )];
  y & " years, " & Month ( md ) & " months, " & Day ( md ) & " days"
)

The reason it works is that FileMaker is good at turning nonsense into dates (FileMaker stores all dates as integers internally). If you tried...

md = Date ( 0 ; days + 1 ; Year ( MyDateField) )

...in a "real" programming language - it would choke. But not so the case with FileMaker! You can (should) enhance the calc by checking the years, months and days for plurality and modifying the strings accordingly:

Let ([
  today = Get ( CurrentDate ) ;
  days = today - gDate ;
  md = Date ( 0 ; days + 1 ; Year ( gDate) ) ;
  y = Int ( days / 365 ) ;
  months =  If ( Month ( md ) = 12 ; 0 ; Month ( md ) ) ];
  y & " year" & If ( y > 0; "s, " ; ", ") &
  months & "  month" & If ( months  ≠ 1 ; "s, " ; ", " ) &
  Day ( md ) & " day" & If ( Day ( md ) > 0 ; "s" ; "" )
)

Happy age calculating...