Formula for age in years and months

Friday 29 March 2013 @ 11:36 pm

One of my readers needed this formula and even gave it a shot.  But this is a tricky formula. I have had a version for years posted on my formulas page, but I hadn’t tried it for months so I took on the challenge.  The formula below will take a date of birth and any ‘as of’ date and calculate the age in years and months. The ‘as of’ date could be simply today’s date (CurrentDate), or it could be a transaction or visit date from the database. Note that the formula only counts complete years and complete months. It adjusts for shorter months so that a child born on 12/31 would turn two months old on 2/28 (or 2/29 on leap years). I even added a few refinements like suppressing the years value when it is zero and adding the plural ‘s’ only when the years or months are greater than 1.

Note that you only need to modify lines 2 and 3. If you find a situation where it doesn’t work, please let me know.

WhileReadingRecords;
DateVar Birth:= Date ({Your DOB Field}); //put your date of birth field here
DateVar Ann := Date ({Visit Date}); //put your transaction date here or replace with CurrentDate function

NumberVar y :=
(if (Month(Ann) * 100) + Day (Ann) >=(Month(Birth) *100) + Day (Birth)
then Year (Ann) - Year(Birth)
else Year (Ann) - Year(Birth) -1);

NumberVar m :=
(if Day (Ann) >= Day (Birth)
or Month (Ann) <> Month (Ann+1)
then DateDiff ('m', Birth, Ann)
else DateDiff ('m', Birth, Ann) -1) ;

m:= Remainder (m,12);

(if y >0
then Totext (y,0,'') & ' yr' &
(if y>1 then 's' else '') &
' and '
else "")

& Totext (m,0,'') & ' mth' &
(if m>1 then 's' else '')

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server