For a project I needed to figure out how old someone was when they took a test. In some contexts they wanted that in months, but in others they needed Years and months expressed as Years:Months. Those who have struggled with this realize there are several edge cases that add a lot of complexity since what we are really doing is extracting the numeric value of the year, month and day and then performing calculation on those. I've seen attempts to just get the number of days difference between two dates and then do division and rounding, but that just doesn't work.
It is pretty straightforward when each part of the end date - dd, mm and yyyy - are greater than the start date. Since this is about age the end date is always after the start date. However there are several conditions where this breaks down.
- The day of the month for the end date is before the start date
- The month of the year for the end date is before the start date
- Both of those conditions are true.
- The year of the start date is the same years as the end date (ie. less than a year has passed.)
I started basic, ran into all of those edge cases and came up with this solution. It has a lot of moving pieces but seems to cover all the edge cases.Â
Note: due to the complexity of this application I'm actually pulling the dates through connector fields. If you do this you will have to edit those field names if you copy these fields to another object. It will replace then with numerical field references during the copy. You should be good if you are using actual date fields within the object.
Fields
- Date of Birth - date
- Date Taken - dateÂ
- Month Checker - equation
- Day Checker - equation
- Age Years base - equation
- Age Years - 1 - equation
- Age Years - number (conditional)
- Age Months base - equation
- Age Months - 1 - equation
- Age Months - number (conditional)
- Age in Months - equation
- Age in Months:Years - Text Formula
Month Checker Equation (is the end month before or after start month)
(getDateMonth({Date Taken.Date})-getDateMonth({Child.Date of Birth})
Day Checker Equation (is the end day before or after start day)
getDateDayOfMonth({Date Taken.Date}) - getDateDayOfMonth({Child.Date of Birth})
Age Years Base Equation
getDateYear({Date Taken.Date}) - getDateYear({Child.Date of Birth})
Age Years - 1 Equation
{Age Years base}-1
Age Years Conditional Logic
#1 - Same month for both but the end day is before start day
Month Checker is 0
Day Checker lower than 0
Set to a record value Age Years -1
#2 - Same month for both but the end day is on or after start day
Month Checker is 0
Day Checker higher than -1
Set to a record value Age Years Base
#3- End Month is after start month
Month Checker higher than 0
Set to a record value Age Years Base
#4 - End month is before start month
Month Checker lower than 0
Set to a record value Age Years -1
Age Months base Equation
If end month is the same or after start month just get the difference, otherwise add 12
(getDateMonth({Date Taken.Date})-getDateMonth({Child.Date of Birth})>-1 ? getDateMonth({Date Taken.Date})-getDateMonth({Child.Date of Birth}) : (getDateMonth({Date Taken.Date})-getDateMonth({Child.Date of Birth})+ 12
Age Months - 1 Equation
{Age Months base} -1
Age Months - Conditional Logic
#1 - The same month but the end day is before the start day
Day Checker lower than 0
Age Months -1 is -1
Set to a custom value 11
#2 - End day is the same or after start day
Day Checker higher than -1
Set to a record value Age Months base
#3 -Â End day is the before start day
Day Checker lower than 0
Set to a record value Age Months - 1
Age in Months Equation
{Age Years}*12+{Age Months}
Age in Months:Years Text Formula
{Age Years}:{Age Months}
Hope this helps someone else and if anyone can simplify please comment, but be sure to really test all the edge cases before you do. I got burned on several previous versions that I thought worked.