Friday, October 4, 2013

Contact Age (Years) Formula Field In Salesforce

Due to complications in the human calendar, with leap years and different numbers of days in different months, it's not always easy to calculate the exact human age of a person for CRM purposes. In Salesforce, a more reliable method of calculating the age is through a formula field with the following formula:

( YEAR( TODAY() ) - YEAR( Birthdate ) )

+ IF(
    MONTH( TODAY() ) * 31 + DAY( TODAY() ) >= 
    MONTH( Birthdate ) * 31 + DAY( Birthdate ) , 
    0 , -1 )

The components of this seemingly complex formula are broken down below.

YEAR( TODAY() ) - YEAR( Birthdate )

This gives us the expected age. If a Johnny was born in 2000, and it's now 2013, we would expect the (max) age of Johnny to be 13 once his birthday arrives in 2013.

IF( ... , 0 , -1 )

By assuming that there is a max of 31 days in any given month, treating the month-day relationship as two "digits" in a base-31 number allows us to do a simple arithmetic comparison to see whether the birthday has come and gone in the current year. This works even for the rare February 29 birthdays.

So, if the birthday has passed, then the expected age is fine and we don't need to make any adjustments. If the birthday has not passed, then we subtract one from the max expected age to arrive at the correct current age.