Newest functions Upload a function Leave a tip My account

There are 2,572 functions in this library.

AccurateAgeYMD ( start ; end )

Accurately calculate age between two dates in Years Months Days

  Be the first to rate this function Log in to vote

Tom G - Show more from this author

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

Let ( [ earliest = If ( GetAsDate ( end ) > GetAsDate ( start ) ; start ; end ) ; // Set the earliest of the two dates latest = If ( GetAsDate ( end ) > GetAsDate ( start ) ; end ; start ) ; // Set the latest of the two dates sday = Day ( earliest ) ; // capture the calendar day number of the earliest month eday = Day ( latest ) ; // capture the calendar day number of the latest month days = eday - sday ; // determine the difference (in days) between earliest day and latest day smonth = Month ( earliest ) ; // capture the month of earliest date emonth = Month ( latest ) ; // capture the month of the latest date monthadjust = If ( sday > eday ; -1 ; 0 ) ; // If the start day is greater than the end day, reduce months by 1 months = emonth - smonth + monthadjust ; // determine the difference (in months) between earliest month and latest month syear = Year ( earliest ) ; // capture the year of the earliest date eyear = Year ( latest ) ; // capture the year of the latest date yearadjust = If ( Date ( smonth ; sday ; syear ) > Date ( emonth ; eday ; syear ) ; -1 ; 0 ) ; // If the start day/month is greater then the end day/month of the start year, reduce years by 1 ageyears = eyear - syear + yearadjust ; // difference (in years) between earliest year and latest year, adjusted agemonths = If ( months < 0 ; months + 12; months ) ; // If months are negative, add 12 daysendmonth = Day ( Date ( emonth ; 1 ; eyear ) - 1 ) ; // Determine the number of days in the month before the end month agedays = If ( days < 0 ; daysendmonth + days ; days ) // If days are negative, add daysendmonth ] ; Case ( not ( Length ( start ) and Length ( end ) ) ; "" ; // return blank if either parameter is empty not IsValid ( GetAsDate ( start ) ) ; "The start date is not valid" ; not IsValid ( GetAsDate ( end ) ) ; "The end date is not valid" ; Case ( not ageyears ; "" ; ageyears = 1 ; ageyears & " year" ; ageyears & " years " ) & Case ( not agemonths ; "" ; agemonths = 1 ; agemonths & " month " ; agemonths & " months " ) & Case ( not agedays ; "" ; agedays = 1 ; agedays & " day" ; agedays & " days" ) ) ) Click to copy

This function accurately calculates the period between two dates in the format "Years Months Days". It can handle leap years, and adjusts for the different number of days of the month.

The example above shows the age of the USA from July 4, 1776, on September 1, 2021.

You can customize the output by modifying the Case statements at the end of the function.

 

Comments

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 21 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: