Date calculations: age, duration, and working days

Intermediate

Build accurate date calculations for age in years, duration in multiple units, and business day counts using FileMaker's date arithmetic.

What you'll learn

How to calculate an accurate age in years that accounts for leap years
How to express a duration in days, weeks, months, and years
How to count working days between two dates
How to find the next business day from a given date

FileMaker stores dates as integers (days since January 1, year 1), making arithmetic straightforward: subtraction yields days, addition advances a date. But calculating accurate age in years (accounting for leap years), duration in multiple units, or business day counts requires careful logic that goes beyond simple subtraction.

1/4

Accurate age in years

Simple (Today - BirthDate) / 365 is wrong for leap years. The correct approach uses the Date() function to check whether this year's birthday has passed.

FileMaker Script

Let ( [
  birth    = Contacts::BirthDate ;
  today    = Get ( CurrentDate ) ;
  thisYear = Year ( today ) ;
  bdayThisYear = Date ( Month ( birth ) ; Day ( birth ) ; thisYear )
] ;
  thisYear - Year ( birth ) - If ( today < bdayThisYear ; 1 ; 0 )
)
// Returns the exact age in completed years

Aggregate functions: Sum, Average, Count, Max, Min in context Time and timestamp arithmetic