Calculating a Contact’s Age Through Salesforce Formulas
If your company needs to track age demographics from either your Contacts, Users, or a custom object representing your customers, it is quite easy to calculate by creating a formula field.
There is one important question to consider in determining a person’s current age: Has this person yet had their birthday in the current year? If they have, then their age can be calculated by subtracting their birth year from the current year. If they have not yet had their birthday, then you’ll need to subtract another year from that difference.
The first thing you need to do is to gather the Contact’s birthday by adding a custom “Date of Birth” date field on your object. It is important that you select a Date field and not a Date/Time field because these formula functions will not accept Date/Time fields. It is also unnecessary (and a bit creepy) to know the exact minute that your Contact was born.
After we have this “DOB__c” field, we create an “Age__c” forumla field that returns a number with no decimal places. We are able to pull the current date by using the Today( ) function within the formula.
For the first part of the formula, we are going to subtract the birth year from the current year.
YEAR(TODAY()) - YEAR(DOB__c)
The second part of the calculation needs to determine if the Contact has already had their birthday this year. If the current month is less than their birth month, they have not had their birthday. If the current month is equal to their birth month, then you have to see if the current day is less than their birth day. In all other situations, the Contact has had their birthday. We’ll write out the condition like this:
OR (
MONTH(TODAY()) < MONTH(DOB__c),
AND(
MONTH(TODAY()) == MONTH(DOB__c),
DAY(TODAY()) < DAY(DOB__c)
)
)
Remember that we subtract an extra year from the age if the person has not yet had their birthday. When we bring the two parts of the formula together, we get:
YEAR(TODAY()) - YEAR(DOB__c) -
IF(
OR (
MONTH(TODAY()) < MONTH(DOB__c),
AND(
MONTH(TODAY()) == MONTH(DOB__c),
DAY(TODAY()) < DAY(DOB__c)
)
),
1,
0
)
And that should accurately calculate your Contact’s age.
If you have any questions about this or other Salesforce formulas, contact us by adding a comment below, or @ reply us on Twitter. We’re on Facebook too!
- Visualforce Charting in Salesforce Winter ’13 - September 25, 2012
- Creating a Geolocation Trigger in Salesforce Winter ’13 - September 12, 2012
- Business Days – The Missing Salesforce Function - August 13, 2012
